MySQL master-slave replication configuration in CentOS6 (note: MySQL is installed via yum source)

The advantages of MySQL replication mainly include the following 3 aspects:
1. If there is a problem with the primary server, you can quickly switch to services provided from the server
2. Can perform query operations from the server, reduce the access pressure on the primary server
3. You can perform backups from the server to avoid services that affect the primary server during backup

Note: In general, only infrequently updated data or low real-time requirements of data can be queried from the server, real-time requirements of high data still need to obtain from the primary server

Master server Configuration
My server is installed with Yum, if you want to configure the file
Set some initial parameters, such as default encoding (MY.CNF) (if no my.cnf, you can cp/usr/share/mysql/my-small.cnf/etc/my.cnf)

Pre-Preparation data:
Two servers
master:192.168.1.111 (primary server)
slave:192.168.1.112 (from server)
Network Interoperability (shutdown firewall, close SELinux) specific methods please see the link

First, the master-slave server for the following operations

1.1, consistent version
1.2, initialization of the table, and in the background to start MySQL
1.3, modify the root password (the newly installed MySQL does not have a password)
Second, MySQL master server Configuration master (192.168.1.111)

1) Modify master server master:

The code is as follows	Copy Code
#vi/etc/my.cnf [Mysqld] Log-bin = mysql-bin#[must] enable binary logging Server-id= 1 #[must be server unique ID, default is 1 Expire-logs-days = 7 #只保留7天的二进制日志 to prevent disk from being filled with logs replicate-do-db= Test #需要做复制的数据库名 binlog-ignore-db = MySQL #不备份的数据库 Binlog-ignore-db = Information_schema

2 to set up an authorized user from the server (create a replication account)

The code is as follows	Copy Code
mysql> grant replication Slave on *.* to ' Mysync ' @ ' 192.168.1.112 ' identified by ' 123456 '; mysql> flush Privileges;

Set up an account Mysync, and only allowed to login from 192.168.1.112 This address, the password is 123456.
(if because the MySQL version of the old and new password algorithm is different, you can set: Set password for ' mysync ' @ ' 192.168.1.112′=old_password (' 123456′))

3 to set the read lock on the primary server to ensure that there is no database operation in order to obtain a consistent snapshot

The code is as follows	Copy Code
Mysql> flush tables with read lock;

4 View the current binary log name and offset value on the primary server

The code is as follows	Copy Code
Mysql> Show master status; +------------------+----------+--------------+------------------+ | File | Position | binlog_do_db | binlog_ignore_db | +------------------+----------+--------------+------------------+ |  mysql-bin.000001 |106 | || +------------------+----------+--------------+------------------+

5 at present, the primary database server has stopped the update operation, generate a backup of the primary database, backup in two ways:

(1) All data of CP
If the primary database service can be stopped, the direct CP data file should be the fastest way to generate snapshots:
(2) Mysqldump Backup Data method

The code is as follows	Copy Code
[Root@localhost ~]#/usr/bin/mysqldump-uroot-p123456 test-l-F >/tmp/test.sql

6 after the primary database has been backed up, the primary database can resume the write operation, and the remaining operations only need to be performed from the server:

mysql> unlock tables;

7 The main database of the consistent backup back to the database, the above compressed packets to the corresponding directory can be

Third, MySQL from the server configuration slave (192.168.1.112)

1 Modify the slave from the server:

The code is as follows	Copy Code
#vi/etc/my.cnf [Mysqld] Log-bin = mysql-bin#[must] enable binary logging Server-id = 2 #[must] server unique ID, default is 1 replicate-do-db = Test #需要做复制的数据库名 replicate-ignore-table = Information_schema #自动跳过的表 binlog-ignore-db = MySQL #不备份的数据库 Read_Only = 1 #只读属性

2 Restore the database backed up on the main library to back up data from the library (before importing)

The code is as follows	Copy Code
Mysql> mysql-uroot-p123456 test-v-F </tmp/test.sql

-V View Import details
-F is when an error is encountered in the middle, you can skip over and continue to execute the following statement

3 reboot from library MySQL

The code is as follows	Copy Code
Service mysqld Restart

4 login from the MySQL command line from the library, execute:

The code is as follows	Copy Code
mysql> Change Master to master_host= ' 192.168.1.111 ', master_user= ' Mysync ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000001 ', master_log_pos=106;

5 to initiate a connection from the library, execute:

The code is as follows	Copy Code
mysql> start slave; #启动从库连接

6 View from library status:

The code is as follows	Copy Code
Mysql> show Slave statusg; #查看连接情况 Slave_io_running:yes Slave_sql_running:yes

If any of the above results occur, the configuration succeeds