Master-slave replication for mysql Learning

Master-slave replication for mysql Learning

This article uses mysql5.5 centos6.5 64-bit

I. Role of master-slave Replication

1. If there is a problem with the master server, you can quickly switch to the slave server.

2. Applications with low real-time requirements or infrequent Updates can perform query operations on the server to reduce the access pressure on the master server. Separate read/write data to achieve load.

3. Data Backup can be performed on the slave server to avoid the impact on the master server during the backup.

2. Build a master-slave replication Environment

Master: 192.168.6.small

Slave: 192.168.6.222

1. Set an authorized user for the slave server on the master server

On the master server, create a user named user2 for the slave server 192.168.6.222. The password is 123.

Mysql> grant all on *. * to user2@192.168.6.222 identified by "123 ";

Parameter description:

Grant: mysql authorization keyword

*. *: All tables in all databases

 

Check whether user authorization is successful:

Mysql> show grants for user2@192.168.6.222;

 

Test whether user2 can be used on the slave server to log on to mysql on the master server.

[Root @ localhost tmp] # mysql-uuser2-p123 test-h192.168.6.133;

 

2. Enable the bin-log of the master server and set the server-id value.

Modify the my. cnf configuration file of the master server:

[Mysqld] # enable mysql's bin-log-bin = mysql-bin # set the value of the master server to 1server-id = 1

3. reset the bin-log: mysql> reset master;

Check the latest bin-log status to see if it is in the starting position: mysql> show master status;

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 107 |
+ ------------------ + ---------- + -------------- + ------------------ +

4. Back up master database data

A. Back up data

B. Update the bin-log.

Here we use mysqldump to back up data and use the-l-F parameter to directly set the read lock and update the bin-log when backing up data.

Mysqldump-uroot-p111111 test-l-F> '/tmp/mysql_back/test. SQL ';

5. Send the data backed up by the master server to the slave server

[Root @ localhost tmp] # scp mysql_back/test. SQL 192.168.6.222:/tmp/mysql_back/

6. Reset the bin-log on the slave server and use the backup data on the slave server.

Mysql> rester master;

[Root @ localhost tmp] # mysql-uroot-p111111 test-v-f </tmp/mysql_back/test. SQL;

7. Configure the my. cnf parameter in the slave server

A. # configure slave server-id = 2 (if multiple slave servers exist, each has a unique server-id)
Server-id = 2

B. # enable bin-log
Log-bin = mysql-bin

C. # configure the host, user name, password, and port number to be synchronized

# Configure The host master-host = 192.168.6.20.# the username the slave will use for authentication when connecting # to The master-required master-user = user2 # the password The slave will authenticate with when connecting to # the master-required master-password = 123 # The port the master is listening on. # optional-defaults to 3306 master-port = 3306

D. Restart mysql to make the configuration file take effect.

[Root @ localhost tmp] # service mysqld restart

If you cannot restart the mysql server by using the following method:

mysql> change master to master_host="192.168.6.224",master_user="user2",master_password="123",master_port=3306,master_log_file="mysql-bin.000002",master_log_pos=107;mysql> slave start;

8. view the slave status

Mysql> show slave status \ G; ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.6.20.master_user: user2 Master_Port: 3306 Connect_Retry: 60 // synchronize bin-log Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: localhost-relay-bin.000002/slave server log every 60 seconds
Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_ SQL _Running: Yes // if the two parameters are yes, the master-slave configuration is successful.

Master_Log_File: indicates the log file name used for master-slave synchronization on the host,

Read_Master_Log_Pos: the location of the log file that was successfully synchronized last time.

If these two items do not match the File and Position values seen on the master server, the synchronization fails.

Iii. Test

1. Add data on the master server and view the bin-log status

mysql> insert into t1 values(13);Query OK, 1 row affected (0.02 sec)mysql> insert into t1 values(14);Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(15);Query OK, 1 row affected (0.01 sec)mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 |      656 |              |                  |+------------------+----------+--------------+------------------+

2. view the slave synchronization status

mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.6.224                  Master_User: user2                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 656               Relay_Log_File: localhost-relay-bin.000002                Relay_Log_Pos: 802        Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

Here we can see that the Postion of the master server is equal to the Read_Master_Log_Pos value of the slave server, and the Slave_IO_Running and Slave_ SQL _Running values are all Yes. In this way, the master-slave configuration of mysql is successful.

4. Common commands for master-slave Replication

1. start slave # start the replication thread

2. stop slave # stop the replication thread

3. show slave status # view slave database status

4. show master logs; # Check the bin-log logs of the master database

5. change master to # dynamically change the configuration to the master server

6. show processlist; # view the running processes of the slave Database