MySQL replication (master-slave, master-master, ssl-based)

1. Prepared host
Node1: 172.16.133.11
Node2: 172.16.133.12
MySQL has been installed.
MySQL installation see: http://5142926.blog.51cto.com/5132926/935652
2. mysql master-slave Replication
Node1: master server
Node2: slave server
(1) node1:
Enable binary log (enabled by default)
Create a user with the copy permission
Set server-id
Node2:
Enable relay logs (disabled by default. You can manually disable binary logs if not needed)
Set server-id
Start the slave service and specify the master server Parameters
Node1: In node1, mysql server binary log is enabled by default, and server-id does not need to be modified. The default value is enough.
Create a user with the copy permission
 

 
 

  
  
mysql>grant replication slave,replication client on *.* to repluser@'172.16.133.%' identified by 'redhat' 
  
  
mysql>flush privileges;  
 
 

Node2: Modify the server-id in the mysql main configuration file my. cnf to 21 and comment out log-bin = mysql-bin.
Add relay-log = mysql-relay
Go to mysql and query the global variable show global variables like '% log %;
 

 
 

  
  
mysql>change master to master_host='172.16.133.11',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000003',master_log_pos=356;  
 
 

Mysql> show slave status/G to check the working status of the slave server. You can see Slave_IO_Running: No and Slave_ SQL _Running: No or no. enable these two items.
 

 
 

  
  
mysql>start slave;  
  
  
mysql>set global read_only=1;  
  
  
mysql>show slave status/G  
 
 

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/19402121S-0.jpg "/>

After the configuration is complete, you can create a test database testdb and a test table t1 in node1.

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940216155-1.jpg "/>

Go to mysql of node2 to view

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940211447-2.jpg "/>

(2) If you do not want the slave server thread to automatically start the mysql service at startup, you can set skip-slave-start = 1 in the slave server.
To prevent the master server from crashing suddenly, you can set it on the master server.
Sync_binlog = 1
Innodb_flush_logs_at_trx_commit = 1
(3) Database Replication Filtering
Master Server
[Mysqld]
Binlog-do-db = magedu
Filter on the master server: no database-related write operations will be recorded in binary logs, so it is best not to set them. Generally, you can set them on the slave server.
Slave Server:
Replicate_do_db
Rpplicate_ignore_db

Replicate_do_table
Replicate_ignore_table

Replicate_wild_do_table
Replicate_wild_ignore_table
Copy only one testdb database on the slave server:
[Mysqld]
Replicate_do_db = testdb
Replicate_do_db = mysql
(4) If the master server is connected to a new slave server only after a long running time, replication will be slow, and backup can be used.
Node1: first apply a read lock to the mysql server
Mysql> flush tables with read lock;
Then, back up the logical volume of the mysql data directory.
Lvcreate-L 50 M-s-p r-n mydata-snap/dev/myvg/mydata
Mysql> show master status; view the current location

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/194021C02-3.jpg "/>

Mysql> unlocak tables (unlock immediately after backup)
Mount/dev/myvg/mydata-snap/mnt
Cd/mnt
Ll
Find. | cpio-o-H newc -- quiet | gzip>/root/alldatabase.gz
Cd
Umount/mnt
Scp alldatabase.gz node2:/root
Mysql> use testdb
Mysql> create table tb2

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/19402160N-4.jpg "/>

Node2:
Gzip-d/root/alldatabase.gz
Cp alldatabase/data/mydata
Cd/data/mydata
Cpio-id <alldatabase
Rm alldatabase
Then you can directly start the service mysqld
Then go to mysql and reset the Master/Slave
Mysql> change master to master_host = '192. 16.133.11 ', master_user = 'repluser', master_password = 'redhat', master_log_file = 'mysql-bin.000003', master_log_pos = 172;
Mysql> start slave;
Mysql> show slave status \ G
Mysql> use testdb;
Mysql> show tables
Backup + Copy completed
(5). semi-synchronous master-slave Replication
Node1:
Mysql> install plugin rpl_semi_sync_master SONAME 'semisync _ master. so ';
Mysql> set global rpl_semi_sync_master_enabled = 1;
Mysql & gt; set global rpl_semi_sync_master_timeout = 1000;
Node2:
Mysql> install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so ';
Mysql> set global rpl_semi_sync_slave_enabled = 1;
Mysql> stop slave IO_THREAD; start slave IO_THREAD;
You can also set global variables as follows:
Set global rpl_semi_sync_master_enabled = 1
Cancel plug-in loading
Mysql> uninstall plugin rpl_semi_sync_master;
Check whether semi_sync on the slave server is Enabled:
Mysql> show global status like 'rpl _ semi % ';
Check whether semi_sync on the master server is enabled. Note that the clients value is 1, which indicates that the master-slave semi-sync replication connection is successful:
(6). mysql master-slave replication based on ssl Transmission
①. Modify the configuration file
Node1:
Server_id = 10
Log_bin = mysql-bin
Sync_binlog = 1 Write the binary file to the disk immediately after the transaction is committed, no longer cache and then write
Node2:
Read_only = 1
② Prepare the certificate and Private Key
I. Create a word visa Server
Node1:
Vim/etc/pki/tls/openssl. cnf
Dir =/etc/pki/CA
(Umask 077; openssl genrsa 2048> private/cakey. pem)
Openssl req-new-x509-key private/cakey. pem-out cacert. pem-days 3655

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940214N3-5.jpg "/>

Mkdir certs crl newcerts
Touch index.txt
Echo 01> serial
Ii. Prepare a private key and issue a certificate for mysql on node1.
Mkdir/usr/local/mysql/ssl
Cd ssl/
(Umask 077; openssl genrsa 1024> mysql. key)
Openssl req-new-key mysql. key-out mysql. csr
Openssl ca-in mysql. csr-out mysql. crt
Cp/etc/pki/CA/cacert. pem/usr/local/mysql/ssl/
Iii. Prepare private keys and issue certificates for mysql on node2
Mkdir/usr/local/mysql/ssl
Cd ssl/
(Umask 077; openssl genrsa 1024> mysql. key)
Openssl req-new-key mysql. key-out mysql. csr
Scp./mysql. csr node1:/root
Iv. issue a certificate for node2
Openssl ca-in mysql. csr-out mysql. crt
Scp./mysql. crt node2:/usr/local/mysql/ssl
Cd/etc/pki/CA
Scp./cacert. pem node2:/usr/local/mysql/ssl
After completion, confirm that the/usr/local/mysql/ssl directory in node1 and node2 contains the four files.

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940215223-6.jpg "/>

③ Enable the ssl function of mysql
Node1:
Mysql> show variables like '% ssl % ';

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/19402145K-7.jpg "/>

Have_openssl and have_ssl are displayed as disabled, indicating that ssl is not enabled.
Edit the main configuration file/etc/my. cnf and add it in [mysqld]
Ssl
Restart the mysql service.
Mysql> show variables like '% ssl % ';

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940211B3-8.jpg "/>

Node2: Same as node1
④ Configure the ssl function of the Master/Slave Server
Node1:
Modify the master configuration file and add the following items in [mysqld ]:
Vim/etc/my. cnf
Ssl-ca =/usr/local/mysql/ssl/cacert. pem
Ssl-cert =/usr/local/mysql/ssl/mysql. crt
Ssl-key =/usr/local/mysql/ssl/mysql. key
Mysql> show variables like '% ssl % ';

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/194021N59-9.jpg "/>

Node2: Same as node1
⑤ Configure the Master/Slave Service
Node1:
Mysql> show master status;

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940211093-10.jpg "/>

Node2: the master-slave service is redefined here, so the content of the experiment above needs to be deleted.
Mysql> slave stop;
Mysql> reset slave;
Mysql> change master to master_host = '2017. 16.133.11 ', master_user = 'repluser', master_password = 'redhat', master_log_file = 'mysql-bin.000008 ', master_log_pos = 107, master_ssl = 1, master_ssl_ca = '/usr/local/mysql/ssl/cacert. pem', master_ssl_cert = '/usr/local/mysql/ssl/mysql. crt ', master_ssl_key ='/usr/local/mysql/ssl/mysql. key ';
Mysql> show slave status \ G

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940211919-11.jpg "/>

Mysql> slave start;
Mysql> show slave status \ G

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940214564-12.jpg "/>

This indicates that the ssl-based mysql master-slave replication is successfully configured.
Use the ssrepl user to log on to the mysql master server and verify
Mysql -- ssl-ca =/usr/local/mysql/ssl/cacert. pem -- ssl-cert =/usr/local/mysql/ssl/mysql. crt -- ssl-key =/usr/local/mysql/ssl/mysql. key-urepluser-h172.16.133.11-p

Log on to mysql

Then

Mysql> \ s

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940214449-13.jpg "/>

The SSL: Cipher in use is DHE-RSA-AES256-SHA indicates that mysql has been built on ssl
3. Master-master Replication
Error-prone and not recommended
(1) create a user with the copy permission on the two servers;
Mysql> grant replication slave, replication client on *. * to repluser @ '2017. 172.% 'identified by 'redhat'
Mysql> flush privileges;
(2). modify the configuration file:
Node1:
[Mysqld]
Server-id = 10
Log-bin = mysql-bin
Auto-increment = 2
Auto-increment-offset = 1
Node2:
[Mysqld]
Server-id = 20
Log-bin = mysql-bin
Auto-increment = 2
Auto-increment-offset = 2
(3 ). if the two servers are newly created and no other write operations are performed, each server only needs to record its own binary log file and event location, and use it as the starting position for copying another server.
Node1:
Mysql> show master status;

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940212209-14.jpg "/>

Node2:
Mysql> show master status;

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1940214642-15.jpg "/>

(4). Specify the server as the master server for the other server:
Node1:
Mysql> change master to master_host = '192. 16.133.12 ', master_user = 'repluser', master_password = 'redhat', master_log_file = 'mysql-bin.000004 ', master_log_pos = 172;
Mysql> slave start;
Node2:
Mysql> change master to master_host = '192. 16.133.11 ', master_user = 'repluser', master_password = 'redhat', master_log_file = 'mysql-bin.000004 ', master_log_pos = 172;
Mysql> slave start;

 

 

Another problem occurs in ssl-based mysql master-slave replication, that is, when ssl is not added to my. cnf,

Mysql> show variables like '% ssl % ';

It is disabled. After it is added, it is enabled and displayed as yes. The problem is that after I add the ssl-ca, ssl-cert, and ssl-key paths, restart the service, I found that he has automatically changed to disabled and can only restart ssl-ca, ssl-cert, and ssl-key after it is deleted again. I am puzzled. Please advise.

To solve this problem, after creating the ssl directory, I did not pay attention to the permissions of crt and so on. Just change the four file owners in the ssl directory to mysql.

This article is from the "Zhou Qinqin" blog, please be sure to keep this source http://zhouyuqin.blog.51cto.com/5132926/956576