Configure MySQL Master-Slave replication (Master-Slave) on CentOS)

Configure MySQL Master-Slave replication (Master-Slave) on CentOS)

The previous article introduced the installation of MySQL without compiling, which is really convenient. Now let's take a look at how to configure MySQL master-slave replication. For database installation, see how to install MySQL without compilation on CentOS6.5. Here I assume that you have installed the MySQL database on two machines separately, and do not start the MySQL database first.

Operate on the Master first

# Edit the my. cmf file and add two lines under [mysqld]

Vi/etc/my. cnf
 
[Mysqld]
Innodb_flush_log_at_trx_commit = 2
Sync_binlog = 1
 
# Modify the following content. Generally, the last three IP addresses are added after mysql-bin.
Log-bin = mysql-bin change to log-bin = mysql-bin-60
 
# Server-id unchanged
Server-id = 1
About innodb_flush_log_at_trx_commit
Why is Innodb 100 times slower than MyISAM? You probably forgot to adjust this value. The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. This is time-consuming. Especially when Battery backed up cache is used. Set to 2 is applicable to many applications, especially the conversion from the MyISAM table. It means writing data to the system cache instead of writing data to the hard disk. Logs are flushed to hard disks per second, so you will not lose updates that exceed 1-2 seconds. Setting 0 is faster, but the security is poor. Even if MySQL fails, the transaction data may be lost. Value 2 can only lose data when the entire operating system is down.

About sync_binlog
Sync_binlog: this parameter is crucial for the MySQL system. It not only affects the performance loss caused by Binlog on MySQL, but also affects the data integrity in MySQL. The settings of the "sync_binlog" parameter are described as follows:
Sync_binlog = 0. After the transaction is committed, MySQL does not perform Disk Synchronization commands such as fsync to refresh the information in binlog_cache to the disk, and Filesystem determines when to synchronize the data, or, after the cache is full, it is synchronized to the disk.
Sync_binlog = n. After each n transaction commits, MySQL runs a Disk Synchronization command such as fsync to forcibly write data in binlog_cache to the disk.

In MySQL, sync_binlog is set to 0 by default, that is, no mandatory disk refresh command is performed. At this time, the performance is the best, but the risk is also the biggest. Because once the system Crash, all binlog information in binlog_cache will be lost. When it is set to "1", it is the safest but the biggest loss of performance. When set to 1, even if the system Crash is used, a transaction not completed in binlog_cache can be lost at most, without any material impact on the actual data. From past experience and related tests, for high-concurrency transaction systems, the system write performance gap between "sync_binlog" and "1" may be as high as 5 times or more.

# Start the primary database service
Service mysqld start
 
# Log on to the database
Mysql-uroot-p Password
 
# Grant permissions to the slave Database Server 192.168.1.61, username repl, and password 123456. Please modify the password according to your actual situation. This is only for demonstration.
Mysql> grant replication slave on *. * to 'repl' @ '192. 168.1.61 'identified by '20140901 ';
 
Mysql> show master status;
Record the value of File and Position, which must be used for slave server configuration later.
Switch to Slave now

Vi/etc/my. cnf
 
# Add two lines under [mysqld]
[Mysqld]
Innodb_flush_log_at_trx_commit = 2
Sync_binlog = 1
 
# Modify the following content. Generally, the last three IP addresses are added after mysql-bin.
Log-bin = mysql-bin change to log-bin = mysql-bin-61
 
# The ID value must be unique.
Server-id = 1 to server-id = 2
 
# Start the slave Database Service
Service mysqld start
 
# Log on to the database
Mysql-uroot-p Password
 
# Execute the following statements
Mysql> change master
Master_host = '1970. 168.1.60 ',
Master_user = 'repl ',
Master_password = '000000 ',
Master_log_file = 'file content ',
Master_log_pos = Position content;
 
# If no error is reported, start the Slave synchronization process.
Mysql> start slave;
 
# Master-slave synchronization check
Mysql> show slave status \ G
 
========================================================== ======
Slave_IO_State:
Master_Host: 192.168.1.60
Master_User: repl
Master_Port: 3306
......
Slave_IO_Running: YES
Slave_ SQL _Running: YES
The following content is omitted ......
========================================================== ======
 
Make sure that the values of Slave_IO_Running and Slave_ SQL _Running are YES, and the master-slave configuration is successful.
Get it done, and close the job.

PS: This is the master-slave configuration configured when the database is installed. There is no data. If there is already a database server, how should I add an slave database server? This still needs to be studied.

How to configure MySQL5.6 Master-Slave in Linux

Master-Slave MySQL and read/write splitting (Amoeba) in Linux

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

This article permanently updates the link address: