Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
public:mysql [2017/03/03 17:19] – <code sql> Nico | public:mysql [2022/07/19 23:44] – [database(s) backup] row lock speech Nico | ||
---|---|---|---|
Line 52: | 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 ' | mysql> GRANT REPLICATION SLAVE ON *.* TO ' | ||
Line 60: | 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; | ||
</ | </ | ||
+ | |||
* use tar to create an archive that includes all databases | * use tar to create an archive that includes all databases | ||
+ | |||
<code bash># tar -cvf / | <code bash># tar -cvf / | ||
+ | |||
* 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 74: | Line 80: | ||
+---------------+----------+--------------+------------------+ | +---------------+----------+--------------+------------------+ | ||
</ | </ | ||
+ | |||
* Release the lock | * Release the lock | ||
+ | |||
<code sql> | <code sql> | ||
mysql> UNLOCK TABLES; | mysql> UNLOCK TABLES; | ||
</ | </ | ||
- | * Here is a backup script you can use (remember to use it on the " | + | * Here is a backup script you can use (on a daily use, remember to use it on the " |
- | < | + | < |
#!/bin/sh | #!/bin/sh | ||
Line 95: | Line 103: | ||
for i in `echo 'show databases;' | for i in `echo 'show databases;' | ||
do | do | ||
- | echo -n " ---> | + | echo -n " ---> |
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}/ | --databases --lock-tables --quick --set-charset $i > ${BACKUPDIR}/ |