**This is an old revision of the document!**
Table of Contents
MySQL Tips&Tricks
set root password
shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password'); mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('password');
grant access to a remote machine
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'client' IDENTIFIED BY 'password' WITH GRANT OPTION;
replication
replication account creation
- Set up an account on the master server that the slave server can use to connect <code sql> mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'slave' IDENTIFIED BY 'slavepass'; </code>
database(s) backup
- Flush all the tables and block write statements <code sql> mysql> FLUSH TABLES WITH READ LOCK; </code>
- use tar to create an archive that includes all databases <code bash># tar -cvf /tmp/mysql-snapshot.tar .</code>
- read the value of the current binary log name and offset on the master: <code sql> mysql > SHOW MASTER STATUS; +—————+———-+————–+——————+
File Position BinlogDoDB BinlogIgnoreDB +—————+———-+————–+——————+
mysql-bin.003 73 test manual,mysql +—————+———-+————–+——————+ </code>
- Release the lock <code sql> mysql> UNLOCK TABLES; </code>
slave setup
- copy and extract database backup on the slave
- add the following lines to slave's my.cnf file
[mysqld] server-id=slave_id
- unpack the snapshot
- Execute the following statement on the slave
mysql> CHANGE MASTER TO -> MASTER_HOST='master_host_name', -> MASTER_USER='master_user_name', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='recorded_log_file_name', -> MASTER_LOG_POS=recorded_log_position;
- Execute START SLAVE on the slave.
troubleshooting
duplicate entry in replication
- stop slave <code> mysql> STOP SLAVE; </code>
- either manually delete the duplicate entry, or skip counter by issuing: <code> mysql> SET GLOBAL SQLSLAVESKIP_COUNTER = 1; </code>
- start slave <code> mysql> START SLAVE; </code>
rebuild database indexes
# mysqlcheck -p -r -q -q <table>
reset root password
- start MySQL without authentification <code> # /usr/local/libexec/mysqld -u mysql –skip-grant </code>
- connect to MySQL and change password <code> # mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 62 to server version: 4.0.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DELETE FROM mysql.user WHERE User='root'; mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; </code>