<rhel6 MySQL replication>

MySQL supports one-way, asynchronous replication, one server acting as the primary server during replication, and one or more other servers charging
When from the server. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. This
Some logs can record updates sent to the slave server. When a primary server is connected from the server, it notifies the primary server
The location of the last successful update that the service read in the log. Receive from the server any updates that have occurred since then, and then seal
Lock and wait for the primary server to notify the new update.
Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you have to be careful,
To avoid conflicts between updates to tables on the primary server and updates made to tables on the server.
One-way replication facilitates robustness, speed, and system management:
1. The master server/slave server setting adds robustness. You can switch to a backup from the server when there is a problem with the primary server
2. Better customer response times can be achieved by slicing the load of client queries between the primary server and the server.
SELECT queries can be sent to the slave server to reduce the query processing load on the primary server. But modify
The statement of the data should still be sent to the master server so that the master server and the server remain synchronized. If the non-update query is primary, the
The load balancing policy is effective, but is typically an update query.
3. Another benefit of using replication is that you can use one to perform backups from the server without interfering with the primary server. During the backup process
The master server can continue to process the update.
MySQL provides database synchronization capabilities, which enable us to implement database redundancy, backup, recovery, load balancing, etc. are
be of great help.

Lab Environment:
RHEL 6 mysql5.1.52
Master 192.168.0.11
Slave1 192.168.0.12
Slave2 192.168.0.13
MySQL copy of AB
Note: MySQL database version, two database version to be the same, or slave than the master version of the high!
Installing the MySQL software on master slave
MySQL software can be downloaded on the http://www.mysql.com[/, source code or RPM package can be, because RHEL6 comes with
MySQL software package, direct Yum installation.
# yum Install MySQL mysql-server-y
Master server Configuration
1) Configuring the/ETC/MY.CNF configuration file
Add a parameter under [mysqld]
Log-bin=mysql-bin #启动二进制日志系统
Binlog-do-db=test #二进制需要同步的数据库名, if you need to synchronize multiple libraries, such as synchronizing Westos
Library, add another line of "Binlog-do-db=westos", and so on
Server-id=1 #必须为 A positive integer value between 1 and 232–1
Binlog-ignore-db=mysql #禁止同步 MySQL Database
2) Start Master
# service Mysqld Start
3) Create a sync account and give permissions
Mysql> GRANT REPLICATION slave,reload,super on *.[email protected]' 192.168.0.12 '
Identified by ' Westos ';
Mysql>flush privileges;
Debugging:
On master, use the following command to view
Mysql> Show master status;
+---------------------+-----------+----------------+--------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+---------------------+-----------+----------------+--------------------+
| mysql-bin.000001| 106 | Test | MySQL |
+---------------------+-----------+----------------+--------------------+
Record the values for File and Position, which are used below.
Slave1server Configuration
1) Configure the/etc/my.cnf file
Add a parameter under [mysqld]
server-id=2 #从服务器 ID number, not the same as the primary ID, if you set multiple slave servers, each slave server must
Must have a unique Server-id value that differs from the primary server and from the other server.
Server-id values can be considered similar to IP addresses: These ID values uniquely identify a replication server cluster
Each server instance in the.
2) Start slave
# service Mysqld Start
3) Execute the command on the SLAVE1
mysql> Change Master to master_host= ' 192.168.0.11 ', master_user= ' Westos ',
Master_password= ' Westos ', master_log_file= ' mysql-bin.000001 ', master_log_pos=106;
Query OK, 0 rows affected (0.28 sec)
mysql> slave start;
Query OK, 0 rows Affected (0.00 sec)
Mysql> show Slave status\g;
.....
Slave_io_running:yes
Slave_sql_running:yes
.....
If all is yes, the i/o,slave_sql thread from the library is turned on correctly. Indicates that the database is synchronizing
You can also see if slave server is synchronized by adding data to the test library on master server, more details, and
Please refer to the MySQL Manual manual for parameter settings.
Note: Ensure that the data on master and slave is consistent before synchronizing.
Related file functions:
1. Mysql-bin.index: Once the server has opened the binary log, it will produce a same name as the 2nd log file, but end with. Index
The file. It is used to track which binary log files exist on the disk. MySQL uses it to locate binary log files.
2. Mysqld-relay-bin.index: This file is functionally similar to Mysql-bin.index, but it is for the relay log, not
Binary log.
3. Master.info: Save the information about master. Do not delete it, otherwise, slave cannot connect to master after a reboot.
4. Relay-log.info: Contains information about the current binary and relay logs in the slave.
This structure can be taken if the write operation is small and the read operation is many. You can distribute the read operations to other slave, thus reducing
The pressure of master. However, when the slave is increased to a certain number, the slave load on the master and the network bandwidth become a serious
The problem. This structure is simple, but it is flexible enough to meet the needs of most applications.
When setting log_slave_updates, you can let slave play the Master of other slave. At this point, slave the SQL thread to execute
Write your own binary logs (binary log), and then other slave can get these events and execute them to effectively mitigate
The pressure of master. As follows:
Add a slave2:
1. Since there is already data on master, and the newly added slave2 does not, you must synchronize the data before you configure replication.
1) Execute the command on master
Mysql> FLUSH TABLES with READ LOCK; #锁表
mysql> mysqldump--all-databases--lock-all-tables > Backup.db
Mysql> UNLOCK TABLES; #表解锁
2) If you use only the MyISAM table, you can use the mysqlhotcopy copy even if the server is running.
# mysqlhotcopy-u root-p westos MySQL Bakcup
2. Add the following settings on the slave1:
# VI/ETC/MY.CNF
....
server-id=2
Log-bin=mysql-bin
Binlog-ignore-db=mysql
Binlog-do-db=test
Log-slave-updates
....
#/etc/init.d/mysqld Restart
3. Create a sync account on slave1 and give permissions
Mysql> GRANT REPLICATION slave,reload,super on *.[email protected]' 192.168.0.12 '
Identified by ' Westos ';
Mysql>flush privileges;
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 217 | Test | MySQL |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
4. Add the configuration on the slave2:
# VI/ETC/MY.CNF
Server-id=3
#/etc/init.d/mysqld Start
5. Execute the command on the Slave2
mysql> Change Master to master_host= ' 192.168.0.12 ', master_user= ' Westos ',
Master_password= ' Westos ', master_log_file= ' mysql-bin.000001 ', master_log_pos=217;
mysql> slave start;
Mysql> show Slave status\g;
.....
Slave_io_running:yes
Slave_sql_running:yes