**This is an old revision of the document!**
Table of Contents
MySQL Tips&Tricks
set root password
shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password'); mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('password');
grant access to a remote machine
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'client' IDENTIFIED BY 'password' WITH GRANT OPTION;
create user with a dedicated database
mysql> CREATE DATABASE db; mysql> GRANT ALL PRIVILEGES ON db.* TO 'user'@'client' IDENTIFIED BY 'password';
query cache
you can see the query cache statistics at any time by issuing:
mysql> SHOW VARIABLES LIKE '%query_cache%';
if all values are 0, the query cache is not working.
enable it by setting the cache size in my.cnf:
query-cache-type = 1 query-cache-size = 32M
one innodb file per table
by default, innodb use a gigantic “ibdata” file where it stores all the databases and tables; but you can have it the nice myisam-way, i.e. one directory for each database, and one or several files per table. just add this line to my.cnf:
innodb_file_per_table
replication
replication account creation
- Set up an account on the master server that the slave server can use to connect
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave' IDENTIFIED BY 'slavepass';
database(s) backup
- Flush all the tables and block write statements
mysql> FLUSH TABLES WITH READ LOCK;
- use tar to create an archive that includes all databases
# tar -cvf /tmp/mysql-snapshot.tar .
- read the value of the current binary log name and offset on the master:
mysql > SHOW MASTER STATUS; +---------------+----------+--------------+------------------+ | File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | mysql-bin.003 | 73 | test | manual,mysql | +---------------+----------+--------------+------------------+
- Release the lock
mysql> UNLOCK TABLES;
- Here is a backup script you can use (remember to use it on the “slave” only, because tables locking will cause your database to be unavailable for writing):
#!/bin/sh BACKUPDIR=/home/mysql_backup LOGFILE=${BACKUPDIR}/my_dump_mysql.log SENDNSCA=/usr/local/sbin/send_nsca\ -H\ ${NAGIOS_HOST}\ -d\ ';;'\ -c\ /usr/local/etc/nagios/send_nsca.cfg HOSTNAME=`/bin/hostname -f | /usr/bin/cut -d '.' -f 1,2` backup_success=1 mkdir -p ${BACKUPDIR} rm -f ${LOGFILE}; for i in `echo 'show databases;' | mysql | grep -v Database`; do echo -n " ---> backuping ${i}..." >> ${LOGFILE} mysqldump --add-drop-table --add-locks --create-options --disable-keys --extended-insert --single-transaction\ --databases --lock-tables --quick --set-charset $i > ${BACKUPDIR}/$i.sql 2>> ${LOGFILE}; if [ $? -eq 0 ]; then echo " OK" >> ${LOGFILE}; else backup_success=0 fi done if [ $backup_success -eq 1 ] then echo "${HOSTNAME};;mysql_backups;;0;OK - backup succeeded at `date`" | ${SENDNSCA} >/dev/null else echo "${HOSTNAME};;mysql_backups;;2;CRITICAL - backup failed at `date`, please check ${LOGFILE} for complete report." | ${SENDNSCA} >/dev/null fi
slave setup
- copy and extract database backup on the slave
- add the following lines to slave's my.cnf file
[mysqld] server-id = slave_id report-host = slave_name
- unpack the snapshot
- Execute the following statement on the slave
mysql> CHANGE MASTER TO -> MASTER_HOST='master_host_name', -> MASTER_USER='master_user_name', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='recorded_log_file_name', -> MASTER_LOG_POS=recorded_log_position;
- Execute START SLAVE on the slave.
optimization
MySQLTuner is a great tool to start with. You can find some good tips on this page (in French).
If MySQLTuner reports fragmented tables, you can check them all with:
mysqlcheck -o --all-databases
troubleshooting
duplicate entry in replication
- stop slave <code sql> mysql> STOP SLAVE; </code>
- either manually delete the duplicate entry, or skip counter by issuing: <code sql> mysql> SET GLOBAL SQLSLAVESKIP_COUNTER = 1; </code>
- start slave <code sql> mysql> START SLAVE; </code>
rebuild database indexes
# mysqlcheck -p -r -q -q <table>
reset root password
- start MySQL without authentification <code> # /usr/local/libexec/mysqld -u mysql –skip-grant </code>
- connect to MySQL and change password <code sql> # mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 62 to server version: 4.0.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DELETE FROM mysql.user WHERE User='root'; mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; </code>