Mysql
Install
# there is no more a mysql package after debian stretch # search for mariadb-server https://packages.debian.org/search?keywords=mariadb-server&suite=stable apt-get install mysql-server
Notes
- To connect with a password protected instance:
--password with no arguments tell the command to ask for a password
mysql --host=127.0.0.1 --port=3306 --user=mysql --password mysql
--password=pass1234 tell the command to use this argument as password
mysql --host=127.0.0.1 --port=3306 --user=mysql --password=pass1234 mysql
System query
List users
root@host:~# mysql -u root mysql> select user from mysql.user;
List databases
root@host:~# mysql -u root mysql> select schema_name from information_schema.schemata; # or show databases;
List db tables
root@host:~# mysql -u root mysql> show tables;
List table columns
root@host:~# mysql -u root mysql> show columns from db_table;
Administration
Disable root password
root@host:~# mysql mysql -u root -p
mysql> update user set password=PASSWORD('') where USER='root';
mysql> flush privileges;
mysql> \q
Create user
root@host:~# mysql mysql -u root mysql> CREATE USER 'db_user' IDENTIFIED BY 'db_pass'; mysql> \q
Create super user
root@host:~# mysql mysql -u root mysql> CREATE USER 'superuser' IDENTIFIED BY 'superuser'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'%' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> \q
Create database
root@host:~# mysql mysql -u root mysql> CREATE DATABASE name_db CHARACTER SET 'utf8'; mysql> \q
Grant permissons
root@host:~# mysql mysql -u root mysql> GRANT ALL PRIVILEGES ON name_db.* TO 'db_user' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> \q
Test user
root@host:~# mysql name_db -u db_user -p mysql> \q
Backup and restore
A single database
- Dump
mysqldump --single-transaction name_db > dbname-dump.sql
or
mysqldump --single-transaction name_db | gzip > dbname-dump.sql.gz
From Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
--single-transaction This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the database
- Restore
mysql name_db < dbname-dump.sql
From Reference Manual :: 6.4.2 Reloading SQL-Format Backups
If the file is a single-database dump not containing CREATE DATABASE and USE statements, create the database first (if necessary):
- Example
# on source host root@host_src:~# mysqldump -u root --single-transaction name_db > dbname-dump.sql # on destination host root@host_dst:~# mysql mysql -u root mysql> CREATE USER 'db_user' IDENTIFIED BY 'db_pass'; mysql> CREATE DATABASE name_db CHARACTER SET 'utf8' mysql> GRANT ALL PRIVILEGES ON name_db.* TO 'db_user' WITH GRANT OPTION; mysql> flush privileges; mysql> \q root@host_dst:~# mysql name_db < dbname-dump.sql
Backup and Recovery all databases
- Backup
From Reference Manual :: 6.3.1 Establishing a Backup Policy
root@host:~# mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
This backup operation acquires a global read lock on all tables at the beginning of the dump (using FLUSH TABLES WITH READ LOCK). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables.
A better choise with user and privileges:
root@host:~# mysqldump -u root --single-transaction --all-databases --flush-logs --lock-tables --flush-privileges | gzip > alldatabase_dump.sql.gz
- Recovery
From Reference Manual :: 6.4.2 Reloading SQL-Format Backups
If the dump file was created by mysqldump with the --all-databases or --databases option, it contains CREATE DATABASE and USE statements and it is not necessary to specify a default database into which to load the data:
root@host:~# mysql -u root < dump.sql