Mysql

From campisano.org
Jump to navigation Jump to search

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

Links