Replication of various architectures in MySQL5.5

This document will introduce the following content:

1. Configure master-slave replication for mysql
2. Configure semi-sync
3. SSL-based Replication
4. Copy Filtering
5. Master Model
 
I. System Environment
 
Master: 192.168.56.101
Slave: 192.168.56.102
MySQL-5.5.24 based
Data replication between the master server and slave server is implemented. We do not repeat mysql installation here. If necessary, refer to other documents, assume that the servers on the two nodes have been installed and can be started properly.
 
Ii. configuring mysql master-slave Replication
 
1. Configure the master server
 
 
1.1 edit the MySQL main configuration file
[Root @ master ~] # Vim/etc/my. cnf // edit the configuration file to ensure that the following two lines exist:
 
Log-bin = mysql-bin // define to enable binary log
Server-id = 1 // define server-id. the IDs of master and slave servers cannot be the same.
 
1.2 create a user with the copy permission
[Root @ master ~] #/Usr/local/mysql/bin/mysql-uroot-p
Mysql> grant replication slave, replication client on *. * TO cpmysql @ '192. 168.56.102 'identified BY 'cpmy'; // create a cpmysql user and set the password TO cpmysql
Mysql> flush privileges; // refresh the authorization table
 
View the binary files and Posotions values currently in use. You need to set these two values on the slave server.
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 358 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.05 sec)
 
 
2. Configure the slave server
 
2.1 edit the MySQL main configuration file
[Root @ slave ~] # Vim/etc/my. cnf
 
# Log-bin = mysql-bin // optional. Disable binary log files.
Server-id = 11 // the server-id cannot be the same as the master server
Relay-log = mysql-relay // enable relay log and name it mysql-relay
 
[Root @ slave ~] # Service mysqld restart // restart mysql for the new configuration to take effect
[Root @ slave ~] #/Usr/local/mysql/bin/mysql-u root-p // log on to mysql as a root user
Make sure relay_log is Enabled:
Mysql> show global variables like 'relay _ log ';
+ --------------- + ------------- +
| Variable_name | Value |
+ --------------- + ------------- +
| Relay_log | mysql-relay |
+ --------------- + ------------- +
1 row in set (0.00 sec)
 
Make sure that the server_id configuration takes effect. It cannot be the same as the master server.
Mysql> show global variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 11 |
+ --------------- + ------- +
1 row in set (0.00 sec)
 
2.2 configure master server Parameters
Mysql> change master to MASTER_HOST = '192. 168.56.101 ', MASTER_USER = 'cpmy', MASTER_PASSWORD = 'cpmy', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 192;
Note: The values of the MASTER_LOG_FILE and MASTER_LOG_POS parameters must be the same as those found on the master server.
 
Start the slave server process
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
Query the status of the slave server. The running statuses of Slave_IO_Running and Slave_ SQL _Running are Yes.
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: cpmysql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 358
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
...
If other exceptions cause startup failure, you can view the mysql error log, which contains detailed error information, for example,/mysqldata/slave. err.
 
3. test whether the master-slave replication configuration is successful.
 
Create a database testdb on the master server
[Root @ master ~] #/Usr/local/mysql/bin/mysql-u root-p
Mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
 
Check whether the copy is successful on the slave server.
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Test |
| Testdb |
+ -------------------- +
5 rows in set (0.05 sec)
You can see that the copy is successful.
 
4. Recommended configurations
 
4.1 enable synchronization of binary logs to files at any time on the master server. By default, binary logs generated by the master server are first cached, the slave server must rely on the binary files generated on the master server to maintain consistency with the master server. Therefore, the master server should generate a binary file and then immediately synchronize it to the binary log file.
 
Modify MySQL's main configuration file my. cnf and add the following lines:
[Root @ master ~] # Vim/etc/my. cnf
 
Sync_binlog = 1
 
[Root @ master ~] # Service mysqld restart // restart the mysql service to make the configuration take effect
Query whether the parameters take effect on the master server
Mysql> show global variables like 'sync _ binlog ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Sync_binlog | 1 |
+ --------------- + ------- +
1 row in set (0.00 sec)
 
4.2. It is applicable to the innodb engine, so that transaction logs that are not generated once are synchronized to the disk before success is returned. This helps to ensure the security of transaction logs and avoid the loss of transaction logs due to special reasons, however, this will also reduce the execution speed when a large number of transaction log operations need to be recorded.
 
Modify Mysql's main configuration file my. cnf and add the following lines:
[Root @ master ~] # Vim/etc/my. cnf
 
Innodb_flush_logs_at_trx_commit = 1
 
4.3 set slave server to read-only to avoid data inconsistency between master and slave servers due to misoperations
 
[Root @ slave ~] # Vim/etc/my. cnf
 
Read_only = 1
 
5. If the master server has been running for a period of time, the slave server cannot be configured based on the above method when it is a new server. You need to back up the backup on the master server:
 
Apply a read lock to mysql on the master server.
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
 
Create a snapshot to back up mysql using a logical volume. Assume that the device mounted to the mysql data directory is/dev/mysqlvg/mysqllv.
[Root @ master ~] # Lvcreate-L 50 M-s-p r-n mysql-snap/dev/mysqlvg/mysqllv
 
After creating a snapshot on the logical volume, view the binary file of the current mysqld and the Position value. After the snapshot is created, immediately release the previously applied read lock.
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 478 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
 
Release the read lock:
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
 
Mount the created logical volume snapshot and back it up to the slave server
[Root @ master ~] # Mount/dev/mysqlvg/mysql-snap/mnt
[Root @ master ~] # Tar cf allmysql.tar/mnt // compress if the data file is too large
 
Copy the archive file to the slave server, overwrite the content to the mysql data directory on the slave server, and remove the logical volume snapshot.
[Root @ master ~] # Umount/mnt
[Root @ master ~] # Lvremove/dev/mysqlvg/mysql-snap
 
The other steps are the same as the slave server configuration above. For details, refer to --> 2.2. Configure the master server parameters. Note that the values of the MASTER_LOG_FILE and MASTER_LOG_POS parameters are the information displayed when the read lock is released.
 
Now you can implement master-slave replication.

1 2 3 Next Page