MySQL 5.5 master-slave synchronization settings Note sharing

First modify the my. cnf configuration of the Master (10.1.123.197 ).

Add the following content to [mysqld:
Copy codeThe Code is as follows:
Log-bin = mysql-bin
Log-bin-index = mysql-bin.index
Server-id = 1
Sync_binlog = 1
Binlog_format = mixed

Specify the database to be synchronized and ignore the database that does not need to be synchronized.
Copy codeThe Code is as follows:
Binlog-do-db = testdb
Binlog-ignore-db = mysql
Binlog-ignore-db = performance_schema
Binlog-ignore-db = information_schema

In [mysqldump], modify the content
Copy codeThe Code is as follows: max_allowed_packet = 32 M

Note: Make sure that max_allowed_packet has a relatively large value, such as max_allowed_packet = 100 M.

Restart mysql:
Copy codeThe Code is as follows: service mysql restart

Create a copy user on the Master
Copy codeThe Code is as follows: create user funsion;
Grant replication slave on *. * TO funsion identified by 'jb51. net ';
# The password in the test environment is xybench.

+ ---------------------------------------------------------- +

Modify the Slave (10.1.123.160) configuration and my. cnf

Add the following content in [mysqld ]:
Copy codeThe Code is as follows:
Server-id = 2
Log-bin = mysql-bin
Relay-log-index = slave-relay-bin.index
Relay-log = slave-relay-bin

Sync_master_info = 1
Sync_relay_log = 1
Sync_relay_log_info = 1

# The above three lines are only used in MySQL 5.5

Enter mysql-u root-p
Enter the mysql command line and enter
Copy codeThe Code is as follows:
Change master to MASTER_HOST = '10. 1.123.233 ', MASTER_USER = 'funsion', MASTER_PORT = 3306, MASTER_PASSWORD = 'ifunsion. com ';

Change master to MASTER_CONNECT_RETRY = 30;

Enter start slave;

+ ----------------------- Cannot be started if an error occurs ----------------- +

Enter the Master database
Enter show master status \ G
Record the currently used log-binfile and position (for example, the current log is a mysql-bin.000001, the location is 107)

Then go to the Slave Database
Enter stop slave;
Copy codeThe Code is as follows: change master to master_log_file = 'mysql-bin.000001 ', master_log_pos = 107;
Finally, enter start slave;

Finally, execute
Copy codeThe Code is as follows: yum-y install ntpdate
Ntpdate cn.pool.ntp.org
Clock-w
Synchronize time of several servers

+ ---------------------------- Some possible commands ------------------------- +

Execute lock table:
Copy codeThe Code is as follows: flush tables with read lock;
The purpose of this step is to avoid any new data in the master database during the Master/Slave creation process. Otherwise, the synchronization settings will be troublesome.

Unlock the master database:
Copy codeThe Code is as follows: unlock tables;

The reset master command deletes all binary log files and clears the binary log index files.
The reset slave command deletes all files used for Slave replication and starts again.

+ ----------------------- Other reference documents (not verified) ------------------------ +

When the database service cannot be stopped, create an image for the slave database:
Case:
Server 1: Master
Server 2: slave
Now, due to load problems, it is necessary to mount the slave server with the same number as the number 3 server.
However, database 1 cannot be stopped, database 2 cannot be stopped, and master-slave synchronization processes cannot be stopped (demanding ).
You can do this:
In the master:
Copy codeThe Code is as follows: mysqldump-uroot-pxxxx-h127.0.0.1 -- databases db1 db2 db3 -- master-data = 1> bak. SQL
Server 3 slave stop;
Then, import bak. SQL to No. 3 slave server.
Copy codeThe Code is as follows: slave start;
Server 3 automatically updates the node at the moment of export.
Because the -- master-data = 1 parameter is added with the change statement at the bottom after the SQL file is exported. If -- master-data = 0, it is not included.
It is very convenient, but it is only suitable for the case where the library is not too large. In this case, the exported database has a total of 6 GB.