CentOS6.4 system MySQL master-slave replication basic configuration tutorial

CentOS6.4 system MySQL master-slave replication basic configuration tutorial

For MySQL databases for general purposes, master-slave replication can be used to back up data (if you want to enable automatic slave nodes to take over after the master node fails, you need more complex configurations, if a hardware fault occurs on the master node, the database server can be manually switched to the backup node (slave node) to continue providing services. The basic master-slave replication configuration is very easy. Here we will make a simple record summary.
We select two servers for MySQL master-slave replication. One m1 serves as the master node and the other nn serves as the slave node.
The MySQL database must be installed on both machines. To uninstall the default installation, run the following command:

1 sudo rpm -e --nodeps mysql 2 yum list | grep mysql

Now you can directly execute the following command on CentOS 6.4 for installation:

1 sudo yum install -y mysql-server mysql mysql-deve

Set the password for the root user:

1 mysqladmin -u root password 'shiyanjun'

Then you can log on directly through the MySQL client:

1 mysql -u root -p

Master node configuration

First, considering the database security and ease of management, we need to add a dedicated replication user on the master node m1 so that any user who wants to copy from the master node must use this account:

1CREATEUSERrepli_user;2GRANTREPLICATION SLAVEON*.*TO'repli_user'@'%'IDENTIFIEDBY'shiyanjun';

Operation authorization is also performed here. Use this account to perform cluster replication. If you want to restrict IP segments, you can also configure authorization here.
Then, on the Master node m1, modify the MySQL configuration file/etc/my. cnf to support Master replication. The modified content is as follows:

01[mysqld]02datadir=/var/lib/mysql03socket=/var/lib/mysql/mysql.sock04user=mysql05# Disabling symbolic-links is recommended to prevent assorted security risks06symbolic-links=007server-id=108log-bin=m-bin09log-bin-index=m-bin.index1011[mysqld_safe]12log-error=/var/log/mysqld.log13pid-file=/var/run/mysqld/mysqld.pid

Server-id indicates the identity of the Master node. The slave node uses this server-id to identify the node as a Master node (the source database server node in the replication architecture ).
If MySQL is currently started, restart the server after modifying the cluster copy Configuration:

1sudoservice mysqld restart

Slave node configuration

Then, configure the Slave node nn Similarly, and modify the MySQL configuration file/etc/my. cnf to support the Server Load balancer copy function. The modified content is as follows:

01[mysqld]02datadir=/var/lib/mysql03socket=/var/lib/mysql/mysql.sock04user=mysql05# Disabling symbolic-links is recommended to prevent assorted security risks06symbolic-links=007server-id=208relay-log=slave-relay-bin09relay-log-index=slave-relay-bin.index1011[mysqld_safe]12log-error=/var/log/mysqld.log13pid-file=/var/run/mysqld/mysqld.pid

Similarly, if MySQL is currently started, restart the server after modifying the cluster replication Configuration:

1sudoservice mysqld restart

Next, you need to direct the Slave node nn to the master node and start Slave replication. Execute the following command:

1CHANGE MASTERTOMASTER_HOST='m1', MASTER_PORT=3306, MASTER_USER='repli_user', MASTER_PASSWORD='shiyanjun';2START SLAVE;

Verify cluster Replication

At this time, you can perform related operations on the master node m1 to verify that the contents of the database on the master node are synchronized from the node nn.
If we have configured master-slave replication, any changes to the MysQL database on the master node m1 will be replicated to the slave node nn, including creating a database, creating tables, inserting updates, and other operations, we will start from the database creation:
Create a table on the master node m1:

01CREATEDATABASEworkflow;02CREATETABLE`workflow`.`project` (03`id`int(11)NOTNULLAUTO_INCREMENT,04`name`varchar(100)NOTNULL,05`type` tinyint(4)NOTNULLDEFAULT'0',06`description`varchar(500)DEFAULTNULL,07`create_at`dateDEFAULTNULL,08`update_at`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,09`status` tinyint(4)NOTNULLDEFAULT'0',10PRIMARYKEY(`id`)11) ENGINE=InnoDBDEFAULTCHARSET=utf8;

View the binlog content on m1 and run the following command:

1SHOW BINLOG EVENTS\G

The binlog content is as follows:

01*************************** 1. row ***************************02Log_name: m-bin.00000103Pos: 404Event_type: Format_desc05Server_id: 106End_log_pos: 10607Info: Server ver: 5.1.73-log, Binlog ver: 408*************************** 2. row ***************************09Log_name: m-bin.00000110Pos: 10611Event_type: Query12Server_id: 113End_log_pos: 19714Info: CREATE DATABASE workflow15*************************** 3. row ***************************16Log_name: m-bin.00000117Pos: 19718Event_type: Query19Server_id: 120End_log_pos: 67121Info: CREATE TABLE `workflow`.`project` (22`id` int(11) NOT NULL AUTO_INCREMENT,23`name` varchar(100) NOT NULL,24`type` tinyint(4) NOT NULL DEFAULT '0',25`description` varchar(500) DEFAULT NULL,26`create_at` date DEFAULT NULL,27`update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,28`status` tinyint(4) NOT NULL DEFAULT '0',29PRIMARY KEY (`id`)30) ENGINE=InnoDB DEFAULT CHARSET=utf8313 rows in set (0.00 sec)

Through the above binlog content, we can see that MySQL's binlog records that information, an event corresponds to a row of records. The organizational structure of these records is as follows:

• Log_name: log name, specify the binlog log name for the record operation, here is the m-bin.000001, which corresponds to what we configured in/etc/my. cnf
• Pos: records the start position of an event.
• Event_type: Event Type
• End_log_pos: records the end position of an event.
• Server_id: Server ID
• Info: Event Description

Then, we can view the replication information on the slave node nn. Run the following command to view the database and table information on the slave node nn:

1SHOW DATABASES;2USE workflow;3SHOW TABLES;4DESCproject;

Let's take a look at the execution of the insert statement. Run the following SQL statement on the master node m1:

1INSERTINTO`workflow`.`project`VALUES(1,'Avatar-II', 1,'Avatar-II project','2014-02-16','2014-02-16 11:09:54', 0);

You can execute a query on the node. You can see records of the INSERT statements executed on the m1 node copied from the node nn:

1SELECT*FROMworkflow.project;

Verify that the replication is successful.

Copy Common commands

Below, we have summarized several commands that are commonly used in MySQL master-slave replication scenarios:

1STOP MASTER;

• Terminate master node Replication

1RESET MASTER;

• Clear master node copy files

1STOP SLAVE;

• Terminate slave node Replication

1RESET SLAVE;

• Clear slave node copy files

1SHOW MASTER STATUS\G;

Result example:

1*************************** 1. row ***************************2File: m-bin.0000013Position: 9564Binlog_Do_DB:5Binlog_Ignore_DB:61 row in set (0.00 sec)

• View master node replication status

1SHOW SLAVE STATUS\G;

Result example:

01*************************** 1. row ***************************02Slave_IO_State: Waiting for master to send event03Master_Host: m104Master_User: repli_user05Master_Port: 330606Connect_Retry: 6007Master_Log_File: m-bin.00000108Read_Master_Log_Pos: 95609Relay_Log_File: slave-relay-bin.00000210Relay_Log_Pos: 109711Relay_Master_Log_File: m-bin.00000112Slave_IO_Running: Yes13Slave_SQL_Running: Yes14Replicate_Do_DB:15Replicate_Ignore_DB:16Replicate_Do_Table:17Replicate_Ignore_Table:18Replicate_Wild_Do_Table:19Replicate_Wild_Ignore_Table:20Last_Errno: 021Last_Error:22Skip_Counter: 023Exec_Master_Log_Pos: 95624Relay_Log_Space: 125225Until_Condition: None26Until_Log_File:27Until_Log_Pos: 028Master_SSL_Allowed: No29Master_SSL_CA_File:30Master_SSL_CA_Path:31Master_SSL_Cert:32Master_SSL_Cipher:33Master_SSL_Key:34Seconds_Behind_Master: 035Master_SSL_Verify_Server_Cert: No36Last_IO_Errno: 037Last_IO_Error:38Last_SQL_Errno: 039Last_SQL_Error:401 row in set (0.00 sec)

• View slave node replication status

1SHOWBINARYLOGS\G

• View the BINLOG list