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

Mysql master-slave replication

In the actual enterprise application environment, a single mysql database is not enough to meet the future business needs. For example, if the server fails, if there is no backup server to provide services, the business will have to stop. In this case, we can perform master-slave replication of mysql database. Direct master-slave replication of mysql requires the same version, and master-slave replication operations can be carried out across platforms.

1. The advantages of using mysql master-slave replication are as follows:

1. Using the architecture of master-slave server, the stability can be improved. If the master server fails, we can use the slave server to provide the service.

2. Processing users' requests separately on the master and slave servers can improve the efficiency of data processing.

3. Copy the data from the master server to the slave server to protect the data from unexpected loss.

2. Description of the experimental environment:

Master server (mysql-master): IP address: 172.25.26.1 MySQL has been installed, no user data.

Slave server (mysql-slave): IP address: 172.25.26.2 MySQL has been installed, no user data.

3. The master-slave replication configuration is as follows:

Operate on the primary server:

1) make sure the following parameters are included in / etc/my.cnf. If not, you need to add them manually and restart the mysql service.

[root@localhost ~] # vim/etc/my.cnf

[mysqld]

Log-bin=mysql-bin boot binaries

Server-id=1 Server ID

2) Log in to mysql, add a backup account in mysql, and authorize it to the slave server.

[root@localhost] # mysql-u root-p

Mysql > create user admin@10.0.144.81 identified by'admin'

Mysql > grant replication slave on *. * toadmin@10.0.144.81 identified by 'admin'; (create an admin user and authorize it to 10.0.144.81. )

3) query the status of the master database and write down the values of FILE and Position, which will be used later when configuring the slave server.

Mysql > show master status

Fourth, operate on the slave server:

1) make sure there are log-bin=mysql-bin and server-id=1 parameters in / etc/my.cnf, and change server-id=1 to server-id=10. After modification, it is as follows:

[mysqld]

Log-bin=mysql-bin boot binaries

Server-id=10 Server ID

2) restart the mysql service.

[root@localhost ~] # / etc/init.d/mysqldrestart

3) Log in to mysql and execute the following statement

[root@localhost] # mysql-u root-p

Mysql > change master to master_host='172.25.26.1',master_user='admin',master_password='admin',master_log_file='mysql-bin.000001',master_log_pos=106

4) start slave synchronization.

Mysql > start slave

5) check the master-slave synchronization. If you see that both Slave_IO_Running and Slave_SQL_Running are Yes, the master-slave replication connection is normal.

Mysql > show slave status\ G

Verify that the configuration is normal and that the mysql master and slave can replicate properly.

Fifth, test master-slave replication

Create a new library on the main database and write a table and some data in the library.

[root@localhost] # mysql-u root-p

Mysql > create database zhangfeng

Mysql > use zhangfeng

Mysql > create table zhang (id int (5), namechar (10))

Mysql > insert into zhang values (2 recorder Zhangfeng')

Verify that it is copied to the data properly from the database.

[root@localhost] # mysql-uroot-p123456

Mysql > show databases

Mysql > select * from zhangfeng.zhang

From the results in the figure above, we can see that mysql master-slave replication is already working, and the data we wrote in the master database has been replicated to our slave database.

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.