mysql administration notes

MySQL server on debian administration notes .



Install on debian
debian-sys-maint user
Passwordless administration commands or SQL statements
Configuration files and MySQL system variables
mysqladmin
Using the standard client for an elementary database exploration.
Binary Backups
mysqlhotcopy
mysqldump --Logical Backups.
Master-Slave Replication.
mysqlcheck.



Now, with the debian MySQL server packet installation
# apt-get install mysql-server
you get the MySQL server version 5.5 along with its prerequisites, basic MySQL tools like the standard MySQL client and the Perl DBI, and some other stuff like mailx.
heirloom-mailx libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl
libmysqlclient18 mysql-client-5.5 mysql-common mysql-server-5.5 mysql-server-core-5.5




The debian-sys-maint user.

The debian package adds the debian-sys-maint MySQL user that can do pretty much everything if logging in locally.
mysql> show grants for 'debian-sys-maint'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for debian-sys-maint@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD '*0123456789ABCDEF12346789082F1970A47EDCBA' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select Host,Super_priv,Create_priv,Grant_priv,Drop_priv from mysql.user where user='debian-sys-maint';
+-----------+------------+-------------+------------+-----------+
| Host      | Super_priv | Create_priv | Grant_priv | Drop_priv |
+-----------+------------+-------------+------------+-----------+
| localhost | Y          | Y           | Y          | Y         |
+-----------+------------+-------------+------------+-----------+
1 row in set (0.01 sec)



The debian-sys-maint user has a "random" password stored at /etc/mysql/debian.cnf.
# ls -l /etc/mysql/debian.cnf 
-rw------- 1 root root 333 Oct 23 16:04 /etc/mysql/debian.cnf




Skip the Password Prompts.

The debian-sys-maint user is used by system scripts, but it is convenient for an administrator to use it in his commands and scripts.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf create yo
# mysqladmin --defaults-file=/etc/mysql/debian.cnf drop yo
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'yo' database [y/N] y
Database "yo" dropped


Another way to skip the password prompt when running a SQL command.
# mysql -u root -p"password" -e "command;"
As far as I know since at least version 5.1.41-3 and upwards commands like the above, do not reveal your password in the current processes snapshot (ps)
root     30510  0.0  0.1  40280  2696 pts/0    S+   07:41   0:00          \_ mysql -u root -px xxxxxx




Configuration files and MySQL system variables.

Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Within the MySQL configuration or option files we may define groups for which we want to set options. A Group name often matches a MySQL helper program name. An options group named "group" starts with [group] in the options or configuration files.

The debian debian-package puts the configuration files in /etc/mysql.

The database table and DB specific options files are stored in /var/lib/mysql

An easy way to display MySQL system variables and their values.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf variables


A quick reference of the MySQL daemon options along with some MySQL system variables and their values.
# mysqld --h -v


MySQL has many logging options (look for log in the MySQL system variables).
General query log keeping is expensive and disabled by default. It may may be enabled permanently in the configuration file /etc/mysql/my.cnf
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1


As of version 5.1, general log keeping may be enabled or disabled at runtime.
# mysql -u root -p"root_paswd"
mysql> show variables like 'general%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | OFF                           |
| general_log_file | /var/lib/mysql/anaxagoras.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql> SET GLOBAL general_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'general%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | ON                            |
| general_log_file | /var/lib/mysql/anaxagoras.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql> SET GLOBAL general_log=0;
Query OK, 0 rows affected (0.08 sec)
mysql> quit
Bye
# cat /var/lib/mysql/anaxagoras.log 
/usr/sbin/mysqld, Version: 5.5.40-0+wheezy1 ((Debian)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
141026 13:21:22    37 Query show variables like 'general%'
141026 13:24:35    37 Query SET GLOBAL general_log=0




mysqladmin and a few usage examples.
mysqladmin is an agent-client suitable for administering MySQL servers.


Check whether the server is alive.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf ping
mysqld is alive



See status.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf status
Uptime: 10189  Threads: 2  Questions: 172  Slow queries: 0  Opens: 171  Flush tables: 1  Open tables: 41  Queries per second avg: 0.016


To view an extended status try.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf extended-status |less


List processes.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf processlist
+----+------------------+-----------+----+---------+------+-------+------------------+
| Id | User             | Host      | db | Command | Time | State | Info             |
+----+------------------+-----------+----+---------+------+-------+------------------+
| 40 | root             | localhost |    | Sleep   | 1940 |       |                  |
| 47 | debian-sys-maint | localhost |    | Query   | 0    |       | show processlist |
+----+------------------+-----------+----+---------+------+-------+------------------+


Kill process with ID 40 and show processes (proc).
# mysqladmin --defaults-file=/etc/mysql/debian.cnf kill 40 proc
+----+------------------+-----------+----+---------+------+-------+------------------+
| Id | User             | Host      | db | Command | Time | State | Info             |
+----+------------------+-----------+----+---------+------+-------+------------------+
| 46 | debian-sys-maint | localhost |    | Query   | 0    |       | show processlist |
+----+------------------+-----------+----+---------+------+-------+------------------+


Reload the MySQL server mysql database grant tables.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf reload
or
# mysqladmin -u root -p"root_passwd" flush-privileges


Clear MySQL status variables in a MySQL instance running on host 192.0.2.10
# mysqladmin -h 192.0.2.10 -u root -p"0210_root_passwsd" flush-status


Shutdown MySQL server.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf ping
mysqld is alive
# mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown
# mysqladmin --defaults-file=/etc/mysql/debian.cnf ping 2>/dev/null
# echo $?
1


Start MySQL server.
# /etc/init.d/mysql start
[ ok ] Starting MySQL database server: mysqld ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..




Use the standard client in an elementary database exploration.

Log in to MySQL as the root using the standard mysql client.
# mysql -u root -p"root_passwd"


Show databases.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| foodb              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
The mysql , information_schema and performance_schema databases come with the MySQL server and they are used by the MySQL server in its operation. The mysql database holds information about users,servers,plugins,timezone,etc and the users may write to it (eg: this is how you add a MySQL user ). The information_schema (read-only to the users) stores information about all the other databases that MySQL maintains. The performance_schema database is used by the MySQL system to provide low level execution monitoring.

Use the foodb database.
mysql> use foodb;


Show all tables in the foodb database.
mysql> show tables;
+------------------+
| Tables_in_foodb  |
+------------------+
| exits            |
+------------------+
1 row in set (0.00 sec)


Describe the schema of the 'exits' table in the 'foodb' DB.
mysql> describe exits;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| su         | int(10) unsigned | NO   | PRI | NULL    |       |
| first_test | datetime         | YES  |     | NULL    |       |
| last_test  | datetime         | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


Find out the number of rows in the table exits.
mysql> SELECT COUNT(*) FROM exits;
+----------+
| COUNT(*) |
+----------+
|   260472 |
+----------+
1 row in set (0.00 sec)



exit
mysql> quit
Bye
#




Binary Database Backup.
If a database contains only MyISAM tables (*.frm,*.MYD,or *.MYI) and the db.opt file you may simply copy it.

Lock tables before copying.
# mysql --defaults-file=/etc/mysql/debian.cnf -e "LOCK TABLES foodb.exits READ;"


The database tables are in /usr/lib/mysql. To copy.
# cp -rp /var/lib/mysql/foodb /bak/mysql/foodb


Unlock tables after copying.
# mysql --defaults-file=/etc/mysql/debian.cnf -e "UNLOCK TABLES;"


To restore the foodb to the same or another MySQL server
copy it to /var/lib/mysql.
# cp -rp  /bak/mysql/foodb /var/lib/mysql/foodb
If you are copying to another MySQL server and you are missing or you do not want to mess with the old /var/lib/mysql/mysql you may want to create a user for the foodb.
mysql> grant all on foodb.* to foodbuser;
mysql> set password for foodbuser = password('foodbuser_passwd');


When I did copy the foodb DB directory to another MySQL server everything worked fine, the origin MySQL server was version 5.1.41-3, and the destination MySQL was version 5.5.40-0.



mysqlhotcopy

A more robust way for doing binary backups of MyISAM tables is mysqlhotcopy --a Perl script that comes with the standard MySQL distribution.
Eg: to copy foodb to another MySQL server using mysqlhotcopy.
dest# mkdir /var/lib/mysql/foodb
orig#mysqlhotcopy --method='scp' --user=root --password=mysqlrootpasswd foodb root@192.0.2.26:/var/lib/mysql


mysqldump --Logical Backups

Good for all storage engines. Logical Backups are text files that contain SQL statements used to restore schemata and data.

Dump backup of foodb in a file.
orig# mysqldump -u root -p"root_passwd" foodb > foodb.sql


Restore the foodb to another MySQL server.
dest# mysqladmin --defaults-file=/etc/mysql/debian.cnf create foodb
dest# mysql --defaults-file=/etc/mysql/debian.cnf foodb < foodb.sql




Master-Slave Replication

Prepare Master ( anaxagoras ) and Slave ( democritus ) MySQL servers.
Enable binary logging, set a server ID number and listen on all interfaces.

Master --host anaxagoras
add in /etc/mysql/my.cnf -configuration group mysqld
[mysqld]
server-id               = 11
log_bin                 = /var/log/mysql/mysql-bin.log
bind-address            = 0.0.0.0
innodb_flush_log_at_trx_commit=1
sync_binlog             = 1  
binlog_do_db            = foodb


Restart MySQL
anaxagoras# service mysql restart


Punch Firewall holes.

Slave --host democritus
Add in /etc/mysql/my.cnf -configuration group mysqld
[mysqld]
server-id               = 12
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = foodb

Restart MySQL
democritus# service mysql restart 


Create a user for replication on the master.
anaxagoras# mysql -u root -p"root_passwd"
mysql> CREATE USER 'repuser'@'192.0.2.0/255.255.255.128';
mysql> SET PASSWORD FOR 'repuser'@'192.0.2.0/255.255.255.128' = password('repuser_password');
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'192.0.2.0/255.255.255.128';
mysql> FLUSH PRIVILEGES;


Obtain master's binary log coordinates.
mysql> USE foodb
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.17 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      107 | foodb        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)



Copy the database with mysqldump , mysqlhotcopy , a cold copy, or something else.

Cold Super Copy.
Copy raw data files to the slave MySQL.
If you are using any InnoDB tables, shutdown MySQL
anaxagoras# mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown 
anaxagoras# rsync -avz -e ssh /var/lib/mysql/ root@democritus:/var/lib/mysql
anaxagoras# scp /etc/mysql/debian.cnf root@democritus:/etc/mysql/


Remove master' s lock.
mysql> UNLOCK TABLES;


Configure the slave MySQL server and start the replication.
democritus# mysql -u root -p"root_passwd"
mysql> CHANGE MASTER TO MASTER_HOST='anaxagoras', MASTER_USER='repuser', MASTER_PASSWORD='repuser_password', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;
mysql> START SLAVE;



See the status in the slave MySQL server.
mysql> SHOW SLAVE STATUS\G




mysqlcheck --A MySQL tables maintance program.

Check all tables in all databases.
# mysqlcheck --defaults-file=/etc/mysql/debian.cnf -A


Analyze tables in database foodb.
# mysqlcheck --defaults-file=/etc/mysql/debian.cnf --analyze foodb


Optimize tables in database foodb.
# mysqlcheck --defaults-file=/etc/mysql/debian.cnf -0 foodb


Repair tables in foodb. Make backups of the tables before 'repairing' them.
# mysqlcheck --defaults-file=/etc/mysql/debian.cnf --debug-info --auto-repair foodb




MySQL administration notes