Mysql5.5 master/slave server configuration for windows and linux

The installation and configuration steps in Linux are as follows:

1. First install MySQL

Install MySQL 5.5.22 on the two MySQL servers 192.168.1.169 (master) and 192.168.1.168 (slave). Because the installation process is relatively simple, there are many tutorials on this site, so I will not elaborate on them.

2. Configure the MySQL master server (192.168.1.169)

Mysql-uroot-p # enter the MySQL console
Create database osyunweidb; # create database osyunweidb
Insert into mysql. user (Host, User, Password) values ('localhost', 'osyunweiuser', password ('20140901'); # create user osyunweiuser

# Create a MySQL master-slave database to synchronize user osyunweidbbak password 123456

Flush privileges; # refresh the system authorization table

# Authorize the user osyunweidbbak to only access the database above the master server 192.168.1.169 from the IP address 192.168.1.168, and only have the database backup permission
Grant replication slave on *. * to 'osyunweidbbak' @ '192. 168.1.168 'identified by '20140901' with grant option;


3. Import the database osyunweidb in MySQL master server 192.168.1.169 to MySQL slave server 192.168.1.168.

1. Export the database osyunweidb

Mysqldump-u root-p osyunweidb>/home/osyunweidbbak. SQL # export the database osyunweidb to/home/osyunweidbbak. SQL on the MySQL master server

Note: Before exporting data, you can log on to the MySQL console and execute the following command:

Flush tables with read lock; # Database read-only lock command to prevent data writing during database export

Unlock tables; # unlock

2. Import the database to the MySQL slave server

Mysql-u root-p # go to the MySQL console of the slave server

Create database osyunweidb; # create a database

Use osyunweidb # enter the database

Source/home/osyunweidbbak. SQL # import the backup file to the database

Mysql-u osyunweidbbak-h 192.168.1.169-p # Test logon to the master server on the slave server

4. Configure the my. cnf file of the MySQL master server

Vi/etc/my. cnf # Edit the configuration file and add the following content in the [mysqld] section.
Server-id = 1 # set the server id. If it is set to 1, it indicates the master server. Note: If this line already exists in the original configuration file, you do not need to add it again.
Log_bin = mysql-bin # Start the MySQ binary log system. Note: If this line already exists in the original configuration file, you do not need to add it again.
Binlog-do-db = osyunweidb # Name of the database to be synchronized. If there are multiple databases, repeat this parameter. Each database has one row.
Binlog-ignore-db = mysql # Do not synchronize the mysql system database
Service mysqld restart # restart MySQL
Mysql-u root-p # enter the mysql console
Show master status; view the master server. The following similar information appears:
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000019 | 7131 | osyunweidb | mysql |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Note: here remember the value of File: mysql-bin.000019 and Position: 7131, will be used later.

5. Configure the MySQL slave server's my. cnf file

Vi/etc/my. cnf # Edit the configuration file and add the following content in the [mysqld] section.
Server-id = 2 # A server-id = 1 line already exists in the configuration file, and its value is changed to 2 to indicate
Log-bin = mysql-bin # Start the MySQ binary log system. Note: If this line already exists in the original configuration file, you do not need to add it again.
Replicate-do-db = osyunweidb # Name of the database to be synchronized. If there are multiple databases, repeat this parameter. Each database has one row.
Replicate-ignore-db = mysql # Do not synchronize the mysql system database
: Wq! # Save and exit
Service mysqld restart # restart MySQL
Note: MySQL 5.1.7 and later versions do not support writing master configuration attributes to the my. cnf configuration file. You only need to write the synchronized database and the database to be ignored.
Mysql-u root-p # enter the MySQL console
Slave stop; # stop the slave synchronization process
Change master to master_host = '192. 168.1.169 ', master_user = 'Oss idbbak', master_password = '000000', master_log_file = 'MySQL-bin.000019', master_log_pos = 192; # execute the synchronization statement.
Slave start; # enable the slave synchronization process
Show slave status \ G # View slave synchronization information, the following content appears
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.169
Master_User: osyunweidbbak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 7131
Relay_Log_File: MySQLSlave-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: osyunweidb
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
1 row in set (0.00 sec)
Note:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
The values of the preceding two parameters are Yes, indicating that the configuration is successful!
 

6. Test whether MySQL master/slave server hot standby is successful

1. Enter the MySQL master server

Mysql-u root-p # enter the MySQL console of the master server

Use osyunweidb # enter the database

Create table test (id int not null primary key, name char (20); # CREATE a test TABLE

2. Log on to the MySQL slave server

Mysql-u root-p # enter the MySQL console

Use osyunweidb # enter the database

Show tables; # view the osyunweidb table structure. A new table named test is displayed, indicating that the database is synchronized successfully.

At this point, the MySQL database configuration master/slave server has completed the dual-machine hot standby instance tutorial.

MYSQL 5.5.20 For Windows master/slave backup configuration

Example:
Master server IP: 192.168.0.1 database version: mysql-installer-5.5.20.0.msi
Slave server IP address: 192.168.0.2 database version: mysql-installer-5.5.20.0.msi

Master server configuration

1. Configure the my. ini file:
Open the my. ini configuration file and add the following two lines of code at the bottom of the file:
Server-id = 1
Log-bin = mysql-bin # enable the binary log function

2. Create a synchronization account and authorize it to the slave server. (Such as root, 111111)

3. Restart the database service.
Net stop mysql55
Net start mysql55

Slave server configuration:

1. Configure the my. ini file:
Open the my. ini configuration file and add the following two lines of code at the bottom of the file:
Server-id = 2
Log-bin = mysql-bin
Binlog-do-db = test # database to be copied and synchronized
Binlog-ignore-db = mysql # databases that do not replicate synchronization
Binlog-ignore-db = paybank # Do not copy the synchronized database
Binlog-ignore-db = performance_schema # databases that do not copy and synchronize data

2. Restart the database service.
Net stop mysql55
Net start mysql55

3. Enter the mysql command line
Mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

Mysql> change master
-> Master_host = "192.168.0.1 ",
-> Master_user = "root ",
-> Master_password = "111111 ";
Query OK, 0 rows affected (0.00 sec)

Mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

Mysql> show slave status \ G
Do not add points at the end of this line. Otherwise, you cannot see the error output.
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 174088685
Relay_Log_File: hatang-ee1113bc-relay-bin.000019
Relay_Log_Pos: 4249
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
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: 174088685
Relay_Log_Space: 7234
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: 0
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)


If we see Yes in 1. row, the synchronization is successful.
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

Synchronization encountered a 1062 error:

Solution 1
Mysql> stop slave;
Mysql> set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1;
Mysql> start slave;
Skip this line of errors. Of course, the data may be inconsistent with that of the primary database. Therefore, you need to restore the latest backup from the primary database to the slave database. During this period, the primary database will not allow users to update the data.

Solution 2
When using solution 1, I often fail to solve the problem due to the large number of items to be skipped or the poor effect. Therefore, after many attempts to improve, solution 2 is available.
1. Add the following line of code to the my. ini file (to skip all 1062 errors)
Slave-skip-errors = 1062
2. Restart the database service.
Net stop mysql55
Net start mysql55

3. Enter the mysql command line
Mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

Mysql> change master
-> Master_host = "192.168.0.1 ",
-> Master_user = "root ",
-> Master_password = "111111 ";
Query OK, 0 rows affected (0.00 sec)

Mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

Mysql> show slave status \ G

The difference between the database and the master database is much different from the original one. Therefore, you need to back up the latest data from the master database to the slave database.

4. Stop synchronization.
Mysql> stop slave;
5. Back up the primary database.
6. Restore the database to the slave database.
7. Disable slave-skip-errors = 1062 in the my. ini file.
8. Restart the database service.
Net stop mysql55
Net start mysql55

9. Enable synchronization.
Mysql> stop slave;
Mysql> start slave;
Mysql> show slave status \ G


Solution 3 (not tried)
Mysql> stop slave;
Mysql> show master status;
Mysql> change master
-> Master_host = "192.168.0.1 ",
-> Master_user = "root ",
-& Gt; master_password = "111111 ",
-> Master_log_file = "mysql-bin.000001 ",
-> Mysql> master_log_pos = 0;
Mysql> start slave;
Mysql> show slave status \ G