- [Show pagesource]
- [Old revisions]
- [[unknown link type]]
- []
This is an old revision of the document!
Table of Contents
MySQL
Backup
GRANT LOCK TABLES, SELECT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'secret-password'; FLUSH privileges;
Replication
Correcting an error
Chyba:
phpmyadmin nemohl neco neco neco
Reseni:
stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status \G ....: YES ....: YES
What replication is not
- Replication is not a backup policy. A mistyped DELETE statement will be replicated on the slave too, and you could end up with two, perfectly synchronized, empty databases. Replication can help protect against hardware failure though.
- Replication is not an answer to all performance problems. Although updates on the slave are more optimized than if you ran the updates normally, if you use MyISAM tables, table-locking will still occur, and databases under high-load could still struggle.
- Replication is not a guarantee that the slave will be in sync with the master at any one point in time. Even assuming the connection is always up, a busy slave may not yet have caught up with the master, so you can't simply interchange SELECT queries across master and slave servers.
Source: http://www.databasejournal.com/features/mysql/article.php/3355201/Database-Replication-in-MySQL.htm
Moving to another server
First import mysql tables whith all users
mysql -u root -p mysql <temp/mysql.sql
Then you can do flush privileges
or use the old password. Finaly import all databases
cd /srv/temp for i in *; do db=${i%%.sql}; echo $db; mysqladmin create $db --password=xxxxx; mysql -u root --password=xxxxxx $db <$i; done
unfortunately in the middle the cycle failed (i caused it intentionaly)
X=0 for i in *; do test $X -eq 0 && mv $i $i.done; if [ $i = 'gld.sql' ]; then X=1; fi; done
Reparing Tables
mysql> CHECK TABLE fixtures; mysql> REPAIR TABLE fixtures; mysql> REPAIR TABLE fixtures USE_FRM;
Performance
Regain root access
From http://www.simplehelp.net/2008/11/26/how-to-reset-a-lost-mysql-root-password/
# /etc/init.d/mysql stop # mysqld_safe --skip-grant-tables & # mysql -u root mysql> use mysql; mysql> update user set password=PASSWORD("NewMySQLPassword") where User="root"; mysql> flush privileges; mysql> quit # /etc/init.d/mysql stop # /etc/init.d/mysql start
linux/mysql.1547296067.txt.gz · Last modified: 2019/01/12 13:27 by admin