MySQL Master Master Sync configuration and master and slave configuration steps

Source: Internet
Author: User

MySQL Master sync configuration Step reprint: http://www.jb51.net/article/33624.htm Create a sync user, modify the/ETC/MY.CNF profile, add the following to it, restart the server odd even MySQL service respectively MySQL Master Master sync configuration
Server name Ip System Mysql
Odd.example.com 192.168.1.116 rhel-5.8 5.5.16
Even.example.com 192.168.1.115 rhel-5.8 5.5.16

Suppose the library to be synchronized is Db_rocky
㈠ Creating a synchronization user
On the odd.
Copy CodeThe code is as follows:
mysql> grant replication Slave on * * to ' water ' @ ' 192.168.1.115 ' identified by ' cdio2010 ';
Query OK, 0 rows Affected (0.00 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
On the even.
Copy CodeThe code is as follows:
mysql> grant replication Slave on * * to ' water ' @ ' 192.168.1.116 ' identified by ' cdio2010 ';
Query OK, 0 rows affected (0.11 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
㈡ Modify the/ETC/MY.CNF configuration file to add the following to it:
On the ODD.
Copy CodeThe code is as follows:
[Mysqld]
Binlog-do-db=db_rocky #需要记录进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
Binlog-ignore-db=mysql #不需要记录进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
Replicate-do-db=db_rocky #需要进行同步的数据库. If you have multiple databases that can be separated by commas, or use multiple replicate-do-db options
Replicate-ignore-db=mysql,information_schema #不需要同步的数据库. If you have multiple databases that can be separated by commas, or use multiple replicate-ignore-db options
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
Log-slave-updates
Sync_binlog=1
Auto_increment_offset=1
auto_increment_increment=2
Slave-skip-errors=all #过滤掉一些没啥大问题的错误
On the even.
Copy CodeThe code is as follows:
[Mysqld]
server-id=2 #设置一个不同的id, notice here in my.cnf there is a default value of 1, change the default value, and cannot add a Server-id
Binlog-do-db=db_rocky #需要记录二进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
Binlog-ignore-db=mysql #不需要记录进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-ignore-db options
#需要同步的数据库
Replicate-do-db=db_rocky #需要进行同步的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
Replicate-ignore-db=mysql,information_schema #不需要同步的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
Log-slave-updates
Sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
Slave-skip-errors=all #过滤掉一些没啥大问题的错误
㈢ Restart the MySQL service on the server odd even, respectively
㈣ on server odd, even view as the primary server status
In odd
Copy CodeThe code is as follows:
Mysql> flush tables with read lock; #防止进入新的数据
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show Master Status\g;
1. Row ***************************
file:mysql-bin.000007
position:438
Binlog_do_db:db_rocky
Binlog_ignore_db:mysql
1 row in Set (0.00 sec)
In even
Copy CodeThe code is as follows:
Mysql> flush tables with read lock;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show Master Status\g;
1. Row ***************************
file:mysql-bin.000008
position:107
Binlog_do_db:db_rocky
Binlog_ignore_db:mysql
1 row in Set (0.01 sec)
㈤ Specify the synchronization location with the Change master statement on the server odd, even, respectively:
In odd
Copy CodeThe code is as follows:
mysql> Change Master to master_host= ' 192.168.1.115 ', master_user= ' water ', master_password= ' cdio2010 ',
-master_log_file= ' mysql-bin.000008 ', master_log_pos=107;
Query OK, 0 rows affected (0.05 sec)
In even
Copy CodeThe code is as follows:
mysql> Change Master to master_host= ' 192.168.1.116 ', master_user= ' water ', master_password= ' cdio2010 ',
-master_log_file= ' mysql-bin.000007 ', master_log_pos=438;
Query OK, 0 rows affected (0.15 sec)
Note: The master_log_file,master_log_pos is determined by the status value identified by the primary server above
Master_log_file corresponds to file,master_log_pos corresponding position
On the odd even.
Copy CodeThe code is as follows:
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)
㈥ starting from the server thread on server odd, even, respectively
Copy CodeThe code is as follows:
mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)
View from server status on server odd, even, respectively:
Copy CodeThe code is as follows:
Odd on
Mysql> show Slave status\g;
1. Row ***************************
The main focus is on the following 2 parameters:
...
...
Slave_io_running:yes
Slave_sql_running:yes
...
...
On even:
Mysql> show Slave status\g;
1. Row ***************************
The main focus is on the following 2 parameters:
...
...
Slave_io_running:yes
Slave_sql_running:yes
...
...
㈦ Test
Copy CodeThe code is as follows:
Even on
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Db_rocky |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.00 sec)
mysql> use Db_rocky;
Database changed
Mysql> Show tables;
Empty Set (0.00 sec)
Mysql> CREATE TABLE water (id int);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT into water values (1);
Query OK, 1 row affected (0.01 sec)
Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)
On the ODD.
Mysql> Show tables;
+--------------------+
| Tables_in_db_rocky |
+--------------------+
| T_rocky |
| Water |
+--------------------+
2 rows in Set (0.00 sec)
Mysql> select * from water;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)-------------------------------------------------------------------------------------------- -------------------------------------------MySQL master-slave configuration
  1. (1) First ensure that the MySQL version on the master and slave server is the same
  2. (2) on the primary server, set up an account from the database, using replication slave to give permissions, such as:
  3. MySQL> GRANT REPLICATION SLAVE on * * to ' slave001 ' @ ' 10.10.10.59 ' identified by
  4. ' 123123 ';
  5. Query OK, 0 rows affected (0.13 sec)
  6. (3) Modify the configuration file of the main database my.cnf, turn on Binlog, and set the value of Server-id, the MySQL service must be restarted after the modification
  7. [Mysqld]
  8. Log-bin=mysql-bin
  9. binlog-ignore-db= MySQL
  10. server-id=1
  11. (4) You can then get the current binary log name and offset of the primary server, the purpose of this operation is to start from the database, the data recovery from this point
  12. MySQL> Show master status\g;
  13. 1. Row ***************************
  14. file:mysql-bin.000003
  15. position:243
  16. binlog_do_db:
  17. binlog_ignore_db:
  18. 1 row in Set (0.00 sec)
  19. (5) OK, now you can stop the master data Update operation, and generate a backup of the primary database, we can go through the mysqldump data to the database, of course, you can directly use the CP command to copy the data file to the database
  20. Note Read LOCK is performed on the primary database before the data is exported to ensure data consistency
  21. MySQL> Flush tables with read lock;
  22. Query OK, 0 rows affected (0.19 sec)
  23. And then the mysqldump.
  24. Mysqldump-h127.0.0.1-p3306-uroot-p Test >/home/chenyz/test.sql
  25. It is best to restore the write operation after the primary database has been backed up
  26. MySQL> Unlock tables;
  27. Query OK, 0 rows affected (0.28 sec)
  28. (6) Copy the Test.sql from the primary data backup to the database and import
  29. (7) then modify the my.cnf from the database, increase the Server-id parameter, specify the user to use for replication, the IP of the primary database server, the port and the file and location where the replication log begins to execute
  30. [Mysqld]
  31. Server-id=2
  32. Log-bin=mysql-bin
  33. Master-host =10.10.10.58
  34. master-user=Test
  35. master-pass=123123
  36. Master-port =3306
  37. master-connect-retry=
  38. replicate-do-db =Test
  39. (8) on the slave server, start the slave process
  40. MySQL> start slave;
  41. (9) Show Salve status verification from the server
  42. MySQL> SHOW SLAVE status\g
  43. 1. Row ***************************
  44. Slave_io_state:waiting for Master to send event
  45. Master_host:localhost
  46. Master_user:root
  47. master_port:3306
  48. Connect_retry:3
  49. master_log_file:mysql-bin.003
  50. read_master_log_pos:79
  51. relay_log_file:gbichot-relay-bin.003
  52. relay_log_pos:548
  53. Relay_master_log_file:mysql-bin. 003
  54. Slave_io_running:yes
  55. Slave_sql_running:yes
  56. (10) OK, now you can do some updates on our master server and then check from the server if it has been updated

MySQL Master Master Sync configuration and master and slave configuration steps

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.