MySQL

Introduction
Here are the main commands that are used regularly to manage a MySQL database.

Connection
To connect localy as a privileged user: mysql -u root -p [-S /path/to/mysql.sock]

where: -u User name -p Ask the password -S Socket file

To connect remotely as a non-privileged user: mysql -u username -p -h server.example.com -P port database_name

where: -u User name -p Ask the password -h DB server -P DB listenning port

To disconnect: mysql> quit

Databases
To see the available databases after logging in: mysql> show databases ;

To select the database to operate on: mysql> use database_name ;

To create a new database: mysql> create database database_name ;

To delete it: mysql> drop database database_name ;

Tables
To see the tables in the previously selected database: mysql> show tables ;

To update the statistics: mysql> analyze table table_name ;

To perform a reorganization: mysql> optimize table table_name ;

To see the status of the tables: mysql> show table status [{FROM | IN} database_name] [LIKE 'pattern' | WHERE expr] ;

Statement File
To execute statements in a file, after logging in and selecting a database: mysql> source /path/to/file.sql ;

Indexes
To create an index: create index index_name on table_name (column_name);

To delete an index: drop index index_name on table_name;

Stored Procedures
To view a stored procedure: show create procedure stored_procedure_name;

To run a stored procedure: call stored_procedure_name('arg1','arg2',...);

Dump
To dump an entire database, the command to execute is very similar to the one used to connect: mysqldump -u root -p -S socket_file database_name > /path/to/file.sql

To perform a dump including store procedures: mysqldump -u root -p -R -S socket_file database_name > /path/to/file.sql

To perform a dump with drop tables: mysqldump -u root -p -R -S socket_file --add-drop-table database_name > /path/to/file.sql

To perform a dump without data (only the DDL of the contained tables): mysqldump -u root -p -R -S socket_file --no-data database_name > /path/to/file.sql

To import the dump previously created: mysql -u root -p -S socket_file database_name < /path/to/file.sql

Once you run the command you have to enter the password, even if you don't see the prompt that asks for it.

Users
To create a user with full powers over an entire database: mysql> CREATE USER  ' user_name ' @ ' %.example.com '  IDENTIFIED BY  ' user_password '  ; mysql> GRANT ALL PRIVILEGES ON `database_name`.* TO  ' user_name ' @ ' %.example.com '  ; mysql> FLUSH PRIVILEGES ;

To create a user who can backup a database with mysqldump: mysql> CREATE USER  ' user_name ' @ ' localhost '  IDENTIFIED BY  ' user_password '  ; mysql> GRANT USAGE ON *.* TO  ' user_name ' @ ' localhost '  ; mysql> GRANT SELECT, LOCK TABLES ON `mysql`.* TO  ' user_name ' @ ' localhost '  ; mysql> GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `database_name`.* TO  ' user_name ' @ ' localhost '  ; mysql> FLUSH PRIVILEGES ;

To view a user's privileges: mysql> show grants for  ' user_name ' @ ' %.example.com '  ;

To view which users can access a particular database: mysql> select Db, User from mysql.db where Db=database_name ;

To change a user's password: mysql> ALTER USER  ' user_name ' @ ' %.example.com '  IDENTIFIED BY  ' new_password ' ;

To change a user's password using the mysql_native_password authentication plugin: mysql> ALTER USER  ' user_name ' @ ' %.example.com '  IDENTIFIED WITH 'mysql_native_password' BY  ' new_password ' ;

To list all users: mysql> select user, host, plugin, ssl_type from mysql.user ;

To delete a user: mysql> drop user  ' user_name ' @ ' %.example.com '  ;

Processes
To list the active processes: mysql> show FULL PROCESSLIST ; +---+--+--+--+-+--+---+---+ | Id   | User     | Host                     | db           | Command | Time | State | Info                  | +---+--+--+--+-+--+---+---+ | 47085 | username | server.example.com:30622 | db_name     | Sleep   |    7 |       | NULL                  | | 47092 | root    | localhost                | NULL         | Query   |    0 | NULL  | show FULL PROCESSLIST | +---+--+--+--+-+--+---+---+ 2 rows in set (0.00 sec)

To stop a process: mysql> KILL 47085;

Maximum Number of Connections
To list the maximum number of accepted connections: mysql> SHOW VARIABLES LIKE 'max_connections'; +-+---+ | Variable_name  | Value | +-+---+ | max_connections | 151  | +-+---+ 1 row in set (0.00 sec)

To list the maximum number of connections used: mysql> SHOW STATUS LIKE 'max_used_connections'; +--+---+ | Variable_name       | Value | +--+---+ | Max_used_connections | 152  | +--+---+ 1 row in set (0.01 sec)

To change (temporarily) the maximum number of accepted connections: mysql> SET GLOBAL max_connections = 300;

To change (permanently) the maximum number of accepted connections: vi /etc/my.cnf max_connections = 300

Binary Logs
To list the present logs: mysql> show binary logs ; +---+---+---+ | Log_name     | File_size | Encrypted | +---+---+---+ | binlog.000025 |      178 | No        | | binlog.000026 |      199 | No        | | binlog.000027 |      178 | No        | | binlog.000028 |      178 | No        | | binlog.000029 |      178 | No        | | binlog.000030 |      199 | No        | +---+---+---+

To delete the logs: mysql> purge binary logs to 'binlog.000030' ;

You can automatically delete logs older than n days: mysql> show variables like 'expire_logs_days' ; +--+---+ | Variable_name   | Value | +--+---+ | expire_logs_days | 0    | +--+---+

mysql> set global expire_logs_days=7 ; mysql> show variables like 'expire_logs_days' ; +--+---+ | Variable_name   | Value | +--+---+ | expire_logs_days | 7    | +--+---+