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

1. The prepared host node1: 172.16.133.11node2: 172.16.133.12 has been installed with MySQLMySQL. For details, see 5142926. blog.51cto. com51329269356522.mysql master-slave replication node1: master server.

1. The prepared host node1: 172.16.133.11node2: 172.16.133.12 has been installed with MySQLMySQL. For details, refer to: slave.

1. Prepared host
Node1: 172.16.133.11
Node2: 172.16.133.12
MySQL has been installed.
For MySQL installation, see:
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

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> 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.

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

Go to mysql of node2 to view

(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

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

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 is enabled on the master server and the VM. 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

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.

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

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 % ';