**This is an old revision of the document!**
Table of Contents
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>mysql> FLUSH TABLES WITH READ LOCK;</code>
- use tar to create an archive that includes all databases <code>shell> tar -cvf /tmp/mysql-snapshot.tar .</code>
- read the value of the current binary log name and offset on the master: <code> mysql > SHOW MASTER STATUS; +—————+———-+————–+——————+
File Position BinlogDoDB BinlogIgnoreDB +—————+———-+————–+——————+
mysql-bin.003 73 test manual,mysql +—————+———-+————–+——————+ </code>
- Release the lock <code>mysql> UNLOCK TABLES;</code>
slave setup
- copy and extract database backup on the slave
- add the following lines to slave's my.cnf file <code> [mysqld] server-id=slaveid </code> * unpack the snapshot * Execute the following statement on the slave <code>mysql> CHANGE MASTER TO → MASTERHOST='masterhostname',
- > MASTERUSER='masterusername', → MASTERPASSWORD='slavepass',
- > MASTERLOGFILE='recordedlogfilename', → MASTERLOGPOS=recordedlog_position;
</code>
- 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>