MySQL Replication

MySQL Master-Master Replication

1. Now we will set up MySQL with master-master replication. First, set the MySQL root user password on both machines where YOUR_PASSWORD_HERE is replaced with your MySQL root password: 
   

   mysqladmin -u root password YOUR_PASSWORD_HERE

2. Create a user replication and grant it privileges on the database. Replace 10.1.1. with the first three octets of your private IP range: 
   

    mysql -u root –p mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicaton'@'10.1.1.%' IDENTIFIED BY 'slave'; mysql> GRANT REPLICATION CLIENT ON *.* TO 'replication'@'10.1.1.%'; mysql> GRANT SUPER ON *.* TO 'replication'@'10.1.1.%'; mysql> GRANT RELOAD ON *.* TO 'replication'@'10.1.1.%'; 

3. Create the Wordpress database and create a db user and password to access the db other than root, replacingmyblog_wordpress, wpadmin,abcd1234 and 10.1.1. with the appropriate values you previously set: 
   

   mysql> CREATE DATABASE myblog_wordpress; mysql> USE myblog_wordpress; mysql> GRANT ALL ON myblog_wordpress.* TO wpadmin@'10.1.1.%' IDENTIFIED BY 'abcd1234'; mysql> GRANT ALL ON myblog_wordpress.* TO wpadmin@localhost IDENTIFIED BY 'abcd1234'; mysql> FLUSH PRIVILEGES; mysql> quit 

Configuring db01

1. Now configure MySQL on db01 by editing my.cnf conf file: 
   

   vi /etc/my.cnf

2. Modify the file to look like this, replacing myblog_wordpress with the actual name of the database and10.1.1.12 with the private IP of db02. Pay extra attention to the lines with the #Different comments: 
   

   [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1  server-id=1 #Different than db02  log-bin log-bin=/var/log/mysqld/db01-bin #Different than db02 log-bin-index=/var/log/mysqld/db01-bin-log.index #Different than db02 binlog-do-db=myblog_wordpress binlog-ignore-db=mysql binlog-ignore-db=test  master-host=10.1.1.12 #Different than db02 master-user=replication master-password=slave  replicate-same-server-id=0 auto-increment-increment=2 auto-increment-offset=1 master-connect-retry=5  relay-log=/var/log/mysqld/db01-relay-bin #Different than db02 relay-log-index=/var/log/mysqld/db01-relay-log.index #Different than db02  expire_logs_days=10 max_binlog_size=500M  [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 

3. Create the log file directories for the new MySQL logging and then restart the service: 
   

   mkdir /var/log/mysqld chown mysql:mysql /var/log/mysqld service mysqld restart 

Configuring db02

1. Now configure MySQL on db02 by editing my.cnf conf file: 
   

   vi /etc/my.cnf

2. Modify the file to look like this, replacing myblog_wordpress with the actual name of the database and10.1.1.11 with the private IP of db01. Pay extra attention to the lines with the #different comments: 
   

   [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1  server-id=2  log-bin log-bin=/var/log/mysqld/db02-bin #Different than db01 log-bin-index=/var/log/mysqld/db02-bin-log.index #Different than db01 binlog-do-db=myblog_wordpress binlog-ignore-db=mysql binlog-ignore-db=test  master-host=10.1.1.11 #Different than db01 master-user=replication master-password=slave  replicate-same-server-id=0 auto-increment-increment=2 auto-increment-offset=2 #Different than db01 master-connect-retry=5  relay-log=/var/log/mysqld/db02-relay-bin #Different than db01 relay-log-index=/var/log/mysqld/db02-relay-log.index #Different than db01  expire_logs_days=10 max_binlog_size=500M  [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 

3. Create the log file directories for the new MySQL logging and then restart the service: 
   

   mkdir /var/log/mysqld chown mysql:mysql /var/log/mysqld service mysqld restart 

Verify the MySQL Replication Status

1. To verify the MySQL replication, enter MySQL and run the following commands: 
   

   mysql -u root –p mysql> SHOW MASTER STATUS; mysql> SHOW SLAVE STATUS\G 

2. The most important lines to check are the following, which should read "Yes" on both DB servers: 
   

   Slave_IO_Running:  Yes Slave_SQL_Running:  Yes 

3. Also important is the following line from SHOW SLAVE STATUS\G which should match the Position value fromSHOW MASTER STATUS; on the other server. The number may differ from what you see below: 
   

   Read_Master_Log_Pos:  98 

4. MySQL replication usually works well and is extremely useful, but the databases can get out of sync under certain conditions. One thing to try is to issue the following commands on both servers: 
   

   mysql> stop slave; mysql> reset master; mysql> reset slave; mysql> start slave;