General

Useful list of MySQL terminal commands can be found here.

Load and dump

mysqldump -p"password" -uusername database_name > dump_file_name.sql

Load backup

mysql -u{user name} -p {database name} < dump.sql 

ref

login

 $ sudo mysql -u{user name} -p 


get list of databases

mysql> show databases;


get list of users

mysql> SELECT User FROM mysql.user;


drop database

mysql> drop database {it's name};


Make a database

mysql> create database {it's name};


Create new Site DB

$ mysql -u root -p $ <password>

now you are in mysql

mysql> create database <new db name>; 
mysql> grant usage on *.* to <new user>@localhost identified by '<new user password>';
mysql> grant all privileges on <new db name>.* to <new user>@localhost;

To generate passwords use this site.

Remove DB Access

create database < databasename >; 
grant usage on *.* to < username >@'%' identified by '< password >'; 
grant all privileges on < databasename >.* to < username >@‘%’;


Start Stop

start the server

/etc/init.d/mysqld start

stop the server

/etc/init.d/mysqld stop

ref


Freeze

log into the db...

mysql -uroot -p <dbname to be frozen>

You can put your whole database to read only mode by this commands:

FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = 1;

and back to normal mode with:

SET GLOBAL read_only = 0; UNLOCK TABLES;


Merging 2 Databases

Export both databases and schema

mysqldump -u root -p --no-create-info --insert-ignore database1 > database1.sql 
mysqldump -u root -p --no-create-info --insert-ignore database2 > database2.sql 
mysqldump -u root -p --no-data database1 > schema.sql

After creating a new database, run

mysql -uroot -p -Ddatabase3 < schema.sql 
mysql -uroot -p -Ddatabase3 < database1.sql 
mysql -uroot -p -Ddatabase3 < database2.sql


Table Size

Inside of the adatabase run the following command

SELECT table_name ,  round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MB FROM information_schema.TABLES WHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;

Mac install

Download and Install

Downlaod the https://dev.mysql.com/downloads/mysql/

  • Install as normal
  • During he install it will prompt with the root password save the password.


Add to Bash

  • Add it to you bash/user profile

vim ~/.bash_profile

export PATH=${PATH}:/usr/local/mysql/bin/

  • Restart the terminal

change root password

  • Log into the mysql

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

If you are Symon and skip the instructions

Instructions, but beware because password is actually authentication_string

Stop mysql from running, I ran into the issue that it won't stop so I had to disable it at boot up

update-rc.d mysql disable

restart the computer with mysql off and start mysql in a mode without passwords

mysqld_safe --skip-grant-tables

reset the root password

mysql -u root mysql> use mysql; mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; **** above might not work new versions are authentication_string mysql> update user set authentication_string=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; mysql> flush privileges; mysql> quit

re enable mysql on startup

update-rc.d mysql enable


ERRORS

Migrating 5.7 > 5.5

when loading and dumping it will not work due to datetime(6), this needs to be changed to just datetime, no brackets.

Stop mysql

mysqladmin -u root -p shutdown

MYSQL won't start

might be a few different things, try checking all the dbs, you will need the root

mysqlcheck --all-databases -u root -p

Safe start of MYSQL

sudo -u mysql mysqld_safe --innodb_force_recovery 4

Corrupted data

Check if it's a table issue, with the server running

mysqlcheck --all-databases -u root -p

If you see a db go bad, run it again and see if it fixed it self, if it didn't we need to fix it... goto /var/lib/

mysqladmin -u root -p shutdown sudo chown bohdan: -R mysql

and then fix the table you found to be bad

myisamchk -r -f -o <the database>/<the table>.* sudo chown mysql: -R mysql

start the server back up

sudo service mysql start