MySQL Master/Slave, semi-sync, Master/Master Replication

MySQL Replication

We know that the binary log of the MySQL database records every clear or potentially changing SQL statement of the database. Therefore, we can achieve the master-slave consistency of mysql based on the binary log. The simple mysql replication process we mentioned here is:
First, there is a dump thread on the mysql Master server, which reads binary logs and sends them to slave.
Then, mysql has an I/O thread on the Slave server (Slave), which is responsible for receiving binary log data sent by the Master and writing the data to the local relay log, at this time, an SQL thread reads an SQL statement every time the relay log is applied from the Slave to achieve consistency between the master and Slave servers.
 
First of all, we need to provide 2 mysql version of the same mysql server, the mysql-5.5.20 selected here, the platform for RedHat5.4
 
1. Install mysql-5.5.20
 
# Groupadd-r mysql
# Useradd-g mysql-r-s/sbin/nologin-M mysql
// Create a user to run the process safely
# Chown-R mysql: mysql/mydata/data
 
# Tar xf mysql-5.5.20-linux2.6-i686.tar.gz-C/usr/local
// Decompress and install the downloaded mysql software
# Cd/usr/local/
# Ln-sv mysql-5.5.20-linux2.6-i686 mysql
# Cd mysql
 
# Chown-R mysql: mysql.
# Scripts/mysql_install_db -- user = mysql -- datadir =/mydata/mysql
// Initialize mysql. Here,/mydata/mysql is used to store mysql Data. We recommend that you mount/mydata to a logical volume to facilitate data backup.
# Chown-R root.
 
# Cd/usr/local/mysql
# Cp support-files/my-large.cnf/etc/my. cnf
# Vim/etc/my. cnf
Thread_concurrency = 2 // modify this item. Our cpu is dual-core
Datadir =/mydata/data // add this item to specify the directory where data is stored
# Cp support-files/mysql. server/etc/rc. d/init. d/mysqld
# Chkconfig -- add mysqld
# Chkconfig mysqld on

Ii. Follow these steps to install mysql in accordance with the system usage specifications and export its development components to the system:
 
# Vim/etc/man. config // output the mysql man manual to the man command search path
MANPATH/usr/local/mysql/man // Add this row
# Ln-sv/usr/local/mysql/include/usr/include/mysql
// Output the mysql header file to the system header file path/usr/include
# Echo '/usr/local/mysql/lib'>/etc/ld. so. conf. d/mysql. conf
// Output the mysql database file to the system database search path
# Ldconfig // Let the system reload the system library
# Vim/etc/profile // modify the PATH environment variable so that the system can directly use mysql-related commands
PATH = $ PATH:/usr/local/mysql/bin // Add this row
 
Iii. Master-slave Replication
Master ip: 192.168.1.10
Slave ip: 192.168.1.11
 
On Master: (operations On the Master server)
# Vim/etc/my. cnf:
Server-id = 1 // id of the master server
Log-bin = mysql-bin // enable binary log
Sync_binlog = 1 // The transaction commit is immediately written to the binary log
Innodb_flush_logs_at_trx_commit = 1 // when a transaction is committed, the binary log is immediately written to the disk.
# Mysql-uroot-p
Mysql> grant replication client, replication slave on *. * to cclo @ '2017. 192.% 'identified by '20160301 ';
// Create a user with the username cclo and password 12345. The permissions are client and replication.
Mysql> flush privileges;
Mysql> show grants for cclo @ '192. 192.% '; // verify the permission of this user
 
On Slave: (operations On the Slave server)
# Vim/etc/my. cnf
[Mysqld]
Server-id = 11 // The value must be different from that of the Master node.
# Log-bin = mysql-bin // The binary log of slave is generally unavailable and temporarily disabled
Relay-log = relay-bin // enable relay log
Relay-log-index = relay-bin.index
Read_only = 1 // This item prevents normal users from writing data to the slave server
Skip_slave_start = 1
// After the mysql service is disabled, the master-slave replication is skipped when the service is enabled (if you need to enable it automatically, this option is skipped)
# Service mysqld restart
# Mysql-uroot-p
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| MySQL-bin.000003 | 374 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Mysql> change master to master_host = '192. 168.1.10 ', master_user = 'o o', master_password = '000000', master_log_file = 'mysql-bin.000003', master_log_pos = 192;
// Specify the ip address of the Master and the location of the copied user and binary log
Mysq> start slave; // start master-slave Replication
Mysql> show slave status; // query the working attributes of the slave server to ensure the following two items
Slave_IO_Runing: Yes
Slave_ SQL _Runing: Yes // make sure the two items are in the Yes status

1 2 Next Page