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