Shulou(Shulou.com)06/03 Report--

The following mainly brings you how the MySQL master-slave switch is realized, and I hope that how the MySQL master-slave switch can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

In an one-master and two-slave environment, simulate and test the switching steps when the master library goes down

My system is CentOS6.7 x64

Mysql version 5.5.33 and installed through binary package

In the previous screenshot, the three systems and mysql are all in the same version and installed in the same steps.

[root@mysql-01 ~] # mysql- V

Mysql Ver 14.14 Distrib 5.5.33, for linux2.6 (x86 / 64) using readline 5.1

[root@mysql-01 ~] # uname-r

2.6.32-573.el6.x86_64

Hostname and ip address of the three environments, job title

Mysql-01 192.168.240.137 Master

Mysql-02 192.168.240.138 Slave-01

Msyql-03 192.168.240.139 Slave-02

Actions on Master

Shut down master 192.168.240.137 to simulate downtime in real scenarios

[root@mysql-01 ~] # / etc/init.d/mysqld stop

Shutting down MySQL... SUCCESS!

[root@mysql-01] # lsof-I: 3306

[root@mysql-01 ~] #

Actions on Slave-01

Ensure that all commands in relay log have been executed on the slave database

Execute the show processlist; command on slave, and the result is as follows, indicating that the update is completed.

Slave has read all relay log; waiting for the slave I/O thread to update it

Stop the IO thread on each slave library uplink stop slave io_thread;

Mysql > show processlist

Mysql > stop slave io_thread

Edit the / etc/my.cnf file, and in the [mysqld] directory, turn on the log-bin=mysql-bin option

And note that server-id cannot be the same as server-id on Slave-02

[root@mysql-02 ~] # vim / etc/my.cnf

Log-bin=mysql-bin

Server-id=2

After editing, save and exit, restart the mysql service

[root@mysql-02 ~] # / etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

Log in to mysql on Slave-01

Execute the reset master command to upgrade Slave-01 to Master

Mysql > reset master

Query OK, 0 rows affected (0.01 sec)

And check the master status

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000001

Position: 107

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

Create an account for synchronization and refresh it

Mysql > grant replication slave on *. * to yuci@'%' identified by '123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.01 sec)

Edit the / etc/hosts file and add the ip address and corresponding hostname

(the NAT mode of vmware I used)

[root@mysql-02 ~] # vim / etc/hosts

192.168.240.138 mysql-02

192.168.240.139 mysql-03

Operations on Slave-02

Also add the ip address and the corresponding hostname first

[root@mysql-03 ~] # vim / etc/hosts

192.168.240.138 mysql-02

192.168.240.139 mysql-03

Test whether the yuci user you just created can log in to Slave-01 's database on Slave-02

[root@mysql-03] # mysql- uyuci-p123456-h292.168.240.138

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 5

.

Mysql >

Yuci user logged in successfully, indicating that the account can be used for data synchronization

Log in to the mysql service using the root user to see if all updates are complete

[root@mysql-03] # mysql- uroot-p123456

Mysql > show processlist

Slave has read all relay log; waiting for the slave I/O thread to update it

Stop the slave service

Mysql > stop slave

Query OK, 0 rows affected (0.01 sec)

Enter the main library information and the account number used for synchronization, pay attention to log-file and log-pos, which are the return values from the previous show maste status\ G execution on Slave-01

Mysql > CHANGE MASTER TO

-> MASTER_HOST='192.168.240.138'

-> MASTER_PORT=3306

-> MASTER_USER='yuci'

-> MASTER_PASSWORD='123456'

-> MASTER_LOG_FILE='mysql-bin.000001'

-> MASTER_LOG_POS=107

Query OK, 0 rows affected (0.01 sec)

Enable the slave service

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Check the IO and SQL threads of slave status. The two yes indicates that the switch is complete.

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

So far, the master-slave switch has been completed, and Slave-01 has been upgraded to Master. Its slave CVM is Slave-02.

Create a new database on Slave-01 and see if it can be synchronized

On Slave-01

Mysql > create database tongbu

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | tongbu |

+-+

5 rows in set (0.00 sec)

On Slave-02

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | tongbu |

+-+

5 rows in set (0.00 sec)

For the above about the MySQL master-slave is how to achieve switching, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.