Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
public:mysql [2013/09/13 22:49] – [optimization] defrag Nico | public:mysql [2022/08/04 15:35] (current) – fix indent Nico | ||
---|---|---|---|
Line 12: | Line 12: | ||
<code sql> | <code sql> | ||
mysql> GRANT ALL PRIVILEGES ON *.* TO ' | mysql> GRANT ALL PRIVILEGES ON *.* TO ' | ||
+ | </ | ||
+ | |||
+ | ===== create user with a dedicated database ===== | ||
+ | <code sql> | ||
+ | mysql> CREATE DATABASE db; | ||
+ | mysql> GRANT ALL PRIVILEGES ON db.* TO ' | ||
</ | </ | ||
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: | ||
- | < | + | < |
mysql> SHOW VARIABLES LIKE ' | mysql> SHOW VARIABLES LIKE ' | ||
</ | </ | ||
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 ' | mysql> GRANT REPLICATION SLAVE ON *.* TO ' | ||
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; | ||
</ | </ | ||
+ | |||
* 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 68: | 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 89: | 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}/ | ||
Line 107: | Line 121: | ||
fi | fi | ||
</ | </ | ||
+ | |||
==== slave setup ==== | ==== slave setup ==== | ||
* copy and extract database backup on the slave | * copy and extract database backup on the slave | ||
Line 115: | Line 130: | ||
report-host = slave_name | report-host = slave_name | ||
</ | </ | ||
+ | |||
* unpack the snapshot | * unpack the snapshot | ||
* Execute the following statement on the slave | * Execute the following statement on the slave | ||
- | < | + | < |
mysql> CHANGE MASTER TO | mysql> CHANGE MASTER TO | ||
-> | -> | ||
Line 125: | Line 141: | ||
-> | -> | ||
</ | </ | ||
+ | |||
* Execute START SLAVE on the slave. | * Execute START SLAVE on the slave. | ||
Line 133: | Line 150: | ||
If MySQLTuner reports fragmented tables, you can check them all with: | If MySQLTuner reports fragmented tables, you can check them all with: | ||
- | < | + | |
+ | < | ||
mysqlcheck -o --all-databases | mysqlcheck -o --all-databases | ||
</ | </ | ||
+ | |||
===== troubleshooting ===== | ===== troubleshooting ===== | ||
Line 142: | Line 161: | ||
* stop slave | * stop slave | ||
- | < | + | < |
mysql> STOP SLAVE; | mysql> STOP SLAVE; | ||
</ | </ | ||
* either manually delete the duplicate entry, or skip counter by issuing: | * either manually delete the duplicate entry, or skip counter by issuing: | ||
- | < | + | < |
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; | mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; | ||
</ | </ | ||
* start slave | * start slave | ||
- | < | + | < |
mysql> START SLAVE; | mysql> START SLAVE; | ||
</ | </ | ||
==== rebuild database indexes ==== | ==== rebuild database indexes ==== | ||
- | < | + | < |
- | # mysqlcheck -p -r -q -q < | + | # mysqlcheck -p -r -q -q < |
</ | </ | ||
Line 163: | Line 182: | ||
* start MySQL without authentification | * start MySQL without authentification | ||
- | < | + | |
+ | < | ||
# / | # / | ||
</ | </ | ||
+ | |||
* connect to MySQL and change password | * connect to MySQL and change password | ||
- | < | + | |
+ | < | ||
# mysql | # mysql | ||
Welcome to the MySQL monitor. | Welcome to the MySQL monitor. |