& Lt; rhel6 mysql replication & gt;

MySQL supports one-way and asynchronous replication. One server acts as the master server during the replication process, while one or more other servers
When the slave server. The master server writes updates to the binary log file and maintains an index of the file to track log loops. This
Logs can be recorded for updates sent to the slave server. When an slave server connects to the master server, it notifies the master server
Location where the server reads the last successful update in the log. The slave server receives any updates from that time, and then
Lock and wait for the master server to notify new updates.
Note that when you perform a copy, all updates to the tables in the copy must be performed on the master server. Otherwise, you must be careful,
To avoid conflicts between updates to tables on the master server and those on the slave server.
Unidirectional replication facilitates robustness, speed, and system management:
1. The master server/slave server settings increase robustness. When a problem occurs on the master server, you can switch to the slave server as a backup.
2. By splitting the customer query load between the master server and slave server, a better customer response time can be obtained.
SELECT queries can be sent to the slave server to reduce the query processing load of the master server. But modify
The data statement should still be sent to the master server so that the master server and slave server can be synchronized. If the query is not updated
The load balancing policy is very effective, but generally it is an update query.
3. Another advantage of using replication is that one slave server can be used for backup without interfering with the master server. During the backup process
The master server can continue to process updates.
MySQL provides the Database Synchronization function, which enables database redundancy, backup, recovery, and load balancing.
It is 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 AB Replication
Note: the mysql database version must be the same for both databases, or the slave version is higher than the master version!
Install mysql software on master slave
Mysql software can be downloaded in the http://www.mysql.com [/, source code or rpm package can be, because RHEL6 comes
Mysql software package, directly install yum.
# Yum install mysql-server-y
Master server Configuration
1) configure the/etc/my. cnf configuration file
Add parameters under [mysqld]
Log-bin = mysql-bin # Start the binary log system
Binlog-do-db = test # Name of the database to be synchronized in binary mode. If multiple databases need to be synchronized, for example, westos
Library, then add a line "binlog-do-db = westos", and so on
Server-id = 1 # It must be a positive integer between 1 and 232-1.
Binlog-ignore-db = mysql # disable mysql Database Synchronization
2) Start the master
# Service mysqld start
3) create a synchronization account and grant permissions
Mysql> grant replication slave, RELOAD, super on *. * TO westos @ '2017. 168.0.12'
Identified by 'westos ';
Mysql> Flush privileges;
Debugging:
Run the following command on the master node to view
Mysql> show master status;
+ --------------------- + ----------- + ---------------- + -------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ --------------------- + ----------- + ---------------- + -------------------- +
| Mysql-bin.000001 | 106 | test | mysql |
+ --------------------- + ----------- + ---------------- + -------------------- +
Record the values of File and Position, which will be used below.
Slave1server Configuration
1) configure the/etc/my. cnf File
Add parameters under [mysqld]
Server-id = 2 # slave server ID. Do not use the same master ID as the slave server id. If multiple slave servers are set, each slave server must
There must be a unique server-id value, which must be different from the master server and other slave servers.
Server-id values are considered similar to IP addresses: These IDS can uniquely identify a replication server cluster.
In.
2) Start slave
# Service mysqld start
3) run the following command on 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 yes, the slave database I/O and Slave_ SQL threads are enabled correctly, indicating that the database is being synchronized.
You can also add data to the test database on the master server to check whether the slave server is synchronized. For more information, see
For parameter settings, see the MySQL Manual.
Note: ensure data consistency between the master and slave before synchronization.
Functions of related documents:
1. mysql-bin.index: Once the server enables binary log, it will generate a file with the same name as the binary log file, but ended with. index
. 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: The file's functionality is similar to mysql-bin.index, but it is for relay logs, not
Binary log.
3. master.info: save information about the master. Do not delete it. Otherwise, you cannot connect to the master after the slave is restarted.
4. relay-log.info: contains information about the current binary log and relay log in the slave.
This structure can be used if there are few write operations and many read operations. You can distribute read operations to other slave to reduce
Master pressure. However, when slave increases to a certain number, slave's load on the master and network bandwidth will become a serious problem.
. This structure is simple, but flexible enough to meet the needs of most applications.
When log_slave_updates is set, you can have slave Act as the master of other slave instances. In this case, slave executes the SQL thread
Write your own binary log, and then other slave can get these events and execute them to effectively ease
Master pressure. As follows:
Add an slave2:
1. Because data already exists on the master, but the newly added slave2 does not, you must synchronize the data before configuring replication.
1) execute the command on the master.
Mysql> flush tables with read lock; # LOCK table
Mysql> mysqldump -- all-databases -- lock-all-tables> backup. db
Mysql> unlock tables; # Table UNLOCK
2) If you only use MyISAM tables, you can use mysqlhotcopy to copy them, even if the server is running.
# Mysqlhotcopy-u root-p westos mysql bakcup
2. Add the following settings to 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 synchronization account on slave1 and grant permissions
Mysql> grant replication slave, RELOAD, super on *. * TO westos @ '2017. 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 slave2:
# Vi/etc/my. cnf
Server-id = 3
#/Etc/init. d/mysqld start
5. Run the following command on 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