Basic configuration practices for MySQL master-slave replication in CentOS6.4

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. Basic master

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. Basic master

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:

sudo rpm -e --nodeps mysqlyum list | grep mysql

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

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

Set the password for the root user:

mysqladmin -u root password 'shiyanjun'

Then you can log on directly through the MySQL client:

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:

CREATE USER repli_user;GRANT REPLICATION SLAVE ON *.* TO 'repli_user'@'%' IDENTIFIED BY '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:

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0server-id=1log-bin=m-binlog-bin-index=m-bin.index[mysqld_safe]log-error=/var/log/mysqld.logpid-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:

sudo service 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:

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0server-id=2relay-log=slave-relay-binrelay-log-index=slave-relay-bin.index[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

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

sudo service mysqld restart

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

CHANGE MASTER TO MASTER_HOST='m1', MASTER_PORT=3306, MASTER_USER='repli_user', MASTER_PASSWORD='shiyanjun';START 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:

CREATE DATABASE workflow;CREATE TABLE `workflow`.`project` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(100) NOT NULL,  `type` tinyint(4) NOT NULL DEFAULT '0',  `description` varchar(500) DEFAULT NULL,  `create_at` date DEFAULT NULL,  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `status` tinyint(4) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

SHOW BINLOG EVENTS\G

The binlog content is as follows:

*************************** 1. row ***************************   Log_name: m-bin.000001        Pos: 4Event_type: Format_desc  Server_id: 1End_log_pos: 106       Info: Server ver: 5.1.73-log, Binlog ver: 4*************************** 2. row ***************************   Log_name: m-bin.000001        Pos: 106Event_type: Query  Server_id: 1End_log_pos: 197       Info: CREATE DATABASE workflow*************************** 3. row ***************************   Log_name: m-bin.000001        Pos: 197Event_type: Query  Server_id: 1End_log_pos: 671       Info: CREATE TABLE `workflow`.`project` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(100) NOT NULL,  `type` tinyint(4) NOT NULL DEFAULT '0',  `description` varchar(500) DEFAULT NULL,  `create_at` date DEFAULT NULL,  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `status` tinyint(4) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf83 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:

SHOW DATABASES;USE workflow;SHOW TABLES;DESC project;

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

INSERT INTO `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:

SELECT * FROM workflow.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:

• Terminate master node Replication

STOP MASTER;

• Clear master node copy files

RESET MASTER;

• Terminate slave node Replication

STOP SLAVE;

• Clear slave node copy files

RESET SLAVE;

• View master node replication status

SHOW MASTER STATUS\G;

Result example:

*************************** 1. row ***************************            File: m-bin.000001        Position: 956    Binlog_Do_DB:Binlog_Ignore_DB:1 row in set (0.00 sec)

• View slave node replication status

SHOW SLAVE STATUS\G;

Result example:

*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: m1                  Master_User: repli_user                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: m-bin.000001          Read_Master_Log_Pos: 956               Relay_Log_File: slave-relay-bin.000002                Relay_Log_Pos: 1097        Relay_Master_Log_File: m-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:          Replicate_Ignore_DB:           Replicate_Do_Table:       Replicate_Ignore_Table:      Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:                   Last_Errno: 0                   Last_Error:                 Skip_Counter: 0          Exec_Master_Log_Pos: 956              Relay_Log_Space: 1252              Until_Condition: None               Until_Log_File:                Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:           Master_SSL_CA_Path:              Master_SSL_Cert:            Master_SSL_Cipher:               Master_SSL_Key:        Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 0               Last_SQL_Error:1 row in set (0.00 sec)

• View the BINLOG list

SHOW BINARY LOGS\G

Original article address: basic configuration practices for MySQL master-slave replication in CentOS 6.4. Thanks to the original author for sharing.