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
Last revisionBoth sides next revision
public:mysql [2013/09/13 22:49] – [optimization] defrag Nicopublic:mysql [2022/07/19 23:44] – [database(s) backup] row lock speech Nico
Line 12: Line 12:
 <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>
 +
 +===== 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> </code>
  
Line 18: Line 24:
 you can see the query cache statistics at any time by issuing: you can see the query cache statistics at any time by issuing:
  
-<code>+<code sql>
 mysql> SHOW VARIABLES LIKE '%query_cache%'; mysql> SHOW VARIABLES LIKE '%query_cache%';
 </code> </code>
Line 46: Line 52:
  
   * 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';
Line 54: Line 61:
  
   * Flush all the tables and block write statements   * Flush all the tables and block write statements
 +
 <code sql> <code sql>
 mysql> FLUSH TABLES WITH READ LOCK; mysql> FLUSH TABLES WITH READ LOCK;
 </code> </code>
 +
   * use tar to create an archive that includes all databases   * use tar to create an archive that includes all databases
 +
 <code bash># 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 sql> <code sql>
 mysql > SHOW MASTER STATUS; mysql > SHOW MASTER STATUS;
Line 68: Line 80:
 +---------------+----------+--------------+------------------+ +---------------+----------+--------------+------------------+
 </code> </code>
 +
   * Release the lock   * Release the lock
 +
 <code sql> <code sql>
 mysql> UNLOCK TABLES; mysql> UNLOCK TABLES;
 </code> </code>
  
-  * 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):+  * 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>+<code bash>
 #!/bin/sh #!/bin/sh
  
Line 89: Line 103:
 for i in `echo 'show databases;' | mysql | grep -v Database`; for i in `echo 'show databases;' | mysql | grep -v Database`;
 do do
-  echo -n " ---> backuping ${i}..." >> ${LOGFILE}+  echo -n " ---> backing up ${i}..." >> ${LOGFILE}
   mysqldump --add-drop-table --add-locks --create-options --disable-keys --extended-insert --single-transaction\   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};     --databases --lock-tables --quick --set-charset $i > ${BACKUPDIR}/$i.sql 2>> ${LOGFILE};
Line 117: Line 131:
   * unpack the snapshot   * unpack the snapshot
   * Execute the following statement on the slave    * Execute the following statement on the slave 
-<code>+<code sql>
     mysql> CHANGE MASTER TO     mysql> CHANGE MASTER TO
     ->     MASTER_HOST='master_host_name',     ->     MASTER_HOST='master_host_name',
Line 142: Line 156:
  
   * 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>
Line 167: Line 181:
 </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.