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
note: this will work only for databases created after you enable this option. for this reason it's always better to activate it at the very begining of the server's setup (i.e. before creating or importing any database, or you'll have to dump and reimport all your innodb databases for this to take effect.

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 (on a daily use, remember to use it on the “slave” only, especially if you use MyISAM engine: 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 " ---> backing up ${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 <code sql> mysql> CHANGE MASTER TO
    1. > MASTERHOST='masterhostname', → MASTERUSER='masterusername',
    2. > MASTERPASSWORD='slavepass', → MASTERLOGFILE='recordedlogfilename',
    3. > MASTERLOGPOS=recordedlogposition;

    </code>

  • 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
# /usr/local/libexec/mysqld -u mysql --skip-grant
  • connect to MySQL and change password
# 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;