-Master2/Slave2 : Debian
Introduction
When you are running a critical website, it is important to make sure that you have at least one redundant backup server. This ensures that your database is syncing in real-time. MySQL refers to database syncing as replication
Note:
- Any update on master1 database will update update on slave2 database and reverse.
- Any update on master2 database will update update on slave1 database and reverse.
Setup MySQL Server DB-1
- Any update on master1 database will update update on slave2 database and reverse.
- Any update on master2 database will update update on slave1 database and reverse.
Setup MySQL Server DB-1
Edit /etc/mysql/my.cnf to disable IP binding.
#vim /etc/mysql/my.cnf
Command '#' before this ip
#bind-address = 127.0.0.1
then add this at the end of file:
[mysqld] server-id = 1 log-bin = /var/log/mysql/mysql-bin.log binlog-do-db = exampledb1 #binlog-do-db = exampledb2 (uncommand this if need to replicate more database) replicate-do-db = exampledb1 (use this for replicate from master2) #replicate-do-db = exampledb2
#/etc/init.d/mysql restart
Create a slave user in MySQL by running following commands in the MySQL console.
#mysql -u root -p
mysql>CREATE USER 'replicator'@'%' IDENTIFIED BY 'replicator_password';
mysql>GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’%’ IDENTIFIED BY 'password';
mysql>FLUSH PRIVILEGES;
dump database
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+------------------+ | mysql-bin.006 | 183 | exampledb | | +---------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Then leave the MySQL shell:
mysql> quit;
# mysqldump -u root -p --opt mydb1 mydb2 > mydb.sql
if we want to dump all database:
#mysqldump -u root -p --lock-all-tables --all-databases > alldb.sql
then copy alldb.sql to client server using scp.
mysql> UNLOCK TABLES;
Edit slave mysql configuration file and add following values under [mysqld] section
# vim /etc/my.cnf
command out this ip
#bind-address = 127.0.0.1
server-id=2
binlog-do-db=exampledb1
#binlog-do-db=exampledb2
replicate-do-db=exampledb1
#binlog-do-db=exampledb2
replicate-do-db=exampledb1
#replicate-do-db=exampledb2
log_bin = /var/log/mysql/mysql-bin.log
log_bin = /var/log/mysql/mysql-bin.log
save and exit.
server-id always be an non zero numeric value. These value will never be similar with other master and slave servers.
Restart mysql slave server, If you had already configured replication use –skip-slave-start in start to not to immediate connect to master server.
#/etc/init.d/mysqld restartCreate user replicate for DB-2
#mysql -u root -p
mysql>CREATE USER 'replicator'@'%' IDENTIFIED BY 'replicator_password';
mysql>GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’%’ IDENTIFIED BY 'password';
mysql>FLUSH PRIVILEGES;
mysql>create database mydb;
Restore database backup taken from master server.
# mysql -u root -p mydb < /home/abc/mydb.sql
=>Connect to Server DB-1
mysql -u root -p
Enter password:
mysql>show master status; (take note about master_log_file and Log_POS)
+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.006 | 107 | example | exampledb1,exampledb2 |+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
You’re going to use the File and Position column values in a few.
=>Connect to Server DB-2 (via SQLyog, query browser or console) and type the following sql commands:
mysql>STOP SLAVE;
mysql>CHANGE MASTER TO MASTER_HOST='Server DB-1-ip', MASTER_USER='replicator', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=107;mysql>slave start;
- MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
- MASTER_USER is the user we granted replication privileges on the master.
- MASTER_PASSWORD is the password of MASTER_USER on the master.
- MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
- MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
mysql>START SLAVE;
Check the status of slave server.mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.15
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: mydb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
mysql>
Configure replication from Server DB-2 to Server DB-1
Connect to server DB-2
mysql>SHOW MASTER STATUS;
Take note master log file and pos_log
Connect to server DB-1
-If we want to replicate other database =>we need to edit file /etc/mysql/my.cnf and use command in mysql (mysql>CHANGE MASTER TO MASTER_HOST='Server DB-2-ip', MASTER_USER='replicator', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=1033;) =>that we must update MASTER_LOG_FILE Name and MASTER_LOG_POS
Trouble Shoot:
Connect to server DB-2
mysql>SHOW MASTER STATUS;
Take note master log file and pos_log
Connect to server DB-1
Note:mysql>STOP SLAVE;
mysql>CHANGE MASTER TO MASTER_HOST='Server DB-2-ip', MASTER_USER='replicator', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=1033;mysql>slave start;
-If we want to replicate other database =>we need to edit file /etc/mysql/my.cnf and use command in mysql (mysql>CHANGE MASTER TO MASTER_HOST='Server DB-2-ip', MASTER_USER='replicator', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=1033;) =>that we must update MASTER_LOG_FILE Name and MASTER_LOG_POS
Trouble Shoot:
1-Error message like:
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
=>>>
mysql>stop slave;
mysql>reset slave;
CHANGE MASTER TO MASTER_HOST='10.105.9.203', MASTER_USER='slave_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=107;
mysql>start slave;
----------------------------
how to show server_id:
mysql> SHOW VARIABLES LIKE 'server_id';
mysql>start slave;
----------------------------
how to show server_id:
mysql> SHOW VARIABLES LIKE 'server_id';
=============================+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id | 1 |+---------------+-------+1 row in set (0.01 sec)