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 [2022/07/19 23:25] – [replication account creation] typo Nicopublic:mysql [2022/07/19 23:44] – [database(s) backup] row lock speech Nico
Line 61: 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 75: 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 96: 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};