Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
public:mysql [2008/01/27 11:36] – Nicopublic:mysql [2022/08/04 15:35] (current) – fix indent Nico
Line 1: Line 1:
-====== set root password ======+====== MySQL Tips&Tricks ====== 
 + 
 +===== set root password =====
 <code sql> <code sql>
 shell> mysql -u root shell> mysql -u root
Line 7: Line 9:
  
  
-====== grant access to a remote machine ======+===== grant access to a remote machine =====
 <code sql> <code sql>
 mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'client' IDENTIFIED BY 'password' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'client' IDENTIFIED BY 'password' WITH GRANT OPTION;
 </code> </code>
  
-====== replication ======+===== create user with a dedicated database ===== 
 +<code sql> 
 +mysql> CREATE DATABASE db; 
 +mysql> GRANT ALL PRIVILEGES ON db.* TO 'user'@'client' IDENTIFIED BY 'password'; 
 +</code>
  
-===== replication account creation =====+===== query cache ===== 
 + 
 +you can see the query cache statistics at any time by issuing: 
 + 
 +<code sql> 
 +mysql> SHOW VARIABLES LIKE '%query_cache%'; 
 +</code> 
 + 
 +if all values are 0, the query cache is not working. 
 + 
 +enable it by setting the cache size in my.cnf: 
 + 
 +<code> 
 +query-cache-type = 1 
 +query-cache-size = 32M 
 +</code> 
 + 
 +===== 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: 
 + 
 +<code> 
 +innodb_file_per_table 
 +</code> 
 + 
 +<note>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. 
 +</note> 
 +===== replication ===== 
 + 
 +==== replication account creation ====
  
   * Set up an account on the master server that the slave server can use to connect   * Set up an account on the master server that the slave server can use to connect
 +
 <code sql> <code sql>
 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave' IDENTIFIED BY 'slavepass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave' IDENTIFIED BY 'slavepass';
 </code> </code>
  
-===== database(s) backup =====+ 
 +==== database(s) backup ====
  
   * Flush all the tables and block write statements   * Flush all the tables and block write statements
-<code>mysql> FLUSH TABLES WITH READ LOCK;</code>+ 
 +<code sql> 
 +mysql> FLUSH TABLES WITH READ LOCK; 
 +</code> 
   * use tar to create an archive that includes all databases   * use tar to create an archive that includes all databases
-<code>shell> tar -cvf /tmp/mysql-snapshot.tar .</code>+ 
 +<code bash># tar -cvf /tmp/mysql-snapshot.tar .</code> 
   * read the value of the current binary log name and offset on the master:    * read the value of the current binary log name and offset on the master: 
-<code>+ 
 +<code sql>
 mysql > SHOW MASTER STATUS; mysql > SHOW MASTER STATUS;
 +---------------+----------+--------------+------------------+ +---------------+----------+--------------+------------------+
Line 36: Line 80:
 +---------------+----------+--------------+------------------+ +---------------+----------+--------------+------------------+
 </code> </code>
 +
   * Release the lock   * Release the lock
-<code>mysql> UNLOCK TABLES;</code> + 
-===== slave setup =====+<code sql> 
 +mysql> UNLOCK TABLES; 
 +</code> 
 + 
 +  * 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): 
 + 
 +<code bash> 
 +#!/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 
 +</code> 
 + 
 +==== slave setup ====
   * copy and extract database backup on the slave   * copy and extract database backup on the slave
   * add the following lines to slave's my.cnf file    * add the following lines to slave's my.cnf file 
 <code> <code>
 [mysqld] [mysqld]
-server-id=slave_id+server-id   = slave_id 
 +report-host = slave_name 
 </code> </code>
 +
   * unpack the snapshot   * unpack the snapshot
   * Execute the following statement on the slave    * Execute the following statement on the slave 
-<code>mysql> CHANGE MASTER TO+<code sql> 
 +    mysql> CHANGE MASTER TO
     ->     MASTER_HOST='master_host_name',     ->     MASTER_HOST='master_host_name',
     ->     MASTER_USER='master_user_name',     ->     MASTER_USER='master_user_name',
Line 54: Line 141:
     ->     MASTER_LOG_POS=recorded_log_position;     ->     MASTER_LOG_POS=recorded_log_position;
 </code> </code>
 +
   * Execute START SLAVE on the slave.   * Execute START SLAVE on the slave.
-====== troubleshooting ====== 
  
  
-===== duplicate entry in replication =====+===== optimization ===== 
 + 
 + [[https://github.com/major/MySQLTuner-perl|MySQLTuner]] is a great tool to start with. You can find some good tips on [[http://wiki.deimos.fr/index.php?title=MysqlTuner_:_Optimiser_votre_serveur_MySQL|this page]] (in French). 
 + 
 + If MySQLTuner reports fragmented tables, you can check them all with: 
 + 
 +<code bash> 
 +mysqlcheck -o --all-databases 
 +</code> 
 + 
 +===== troubleshooting ===== 
 + 
 + 
 +==== duplicate entry in replication ====
  
   * stop slave   * stop slave
-<code>+<code sql>
 mysql> STOP SLAVE; mysql> STOP SLAVE;
 </code> </code>
   * either manually delete the duplicate entry, or skip counter by issuing:   * either manually delete the duplicate entry, or skip counter by issuing:
-<code>+<code sql>
 mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
 </code> </code>
   * start slave   * start slave
-<code>+<code sql>
 mysql> START SLAVE; mysql> START SLAVE;
 </code> </code>
  
-===== rebuild database indexes ===== +==== rebuild database indexes ==== 
-<code> +<code bash> 
-  # mysqlcheck -p -r -q -q <table>+# mysqlcheck -p -r -q -q <table>
 </code> </code>
  
  
-===== reset root password =====+==== reset root password ====
  
   * start MySQL without authentification   * start MySQL without authentification
-<code>+ 
 +<code bash>
 # /usr/local/libexec/mysqld -u mysql --skip-grant # /usr/local/libexec/mysqld -u mysql --skip-grant
 </code> </code>
 +
   * connect to MySQL and change password   * connect to MySQL and change password
-<code>+ 
 +<code sql>
 # mysql # mysql
   Welcome to the MySQL monitor.  Commands end with ; or \g.   Welcome to the MySQL monitor.  Commands end with ; or \g.