Shulou(Shulou.com)05/31 Report--

This article mainly explains the "master-slave replication usage of MySQL5.5 database". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "master-slave replication usage of MySQL5.5 database".

Master-slave replication of MySQL 5.5A

Today, I refer to the information on the Internet to study the master-slave replication of the database. Originally, the information on the Internet is already very detailed, but I still encountered a lot of problems in practice. Here is a summary based on the information on the Internet and the problems I have encountered.

System environment: Ubuntu12.04

Software version: mysql-server-5.5

Host IP:192.168.0.200

Slave IP:192.168.0.201

Action:

1. Host operation:

1), edit the mysql configuration file my.cnf

[mysqld]

Server-id=1

Log-bin=mysql-bin

Note: there are some other configurations online, but only these two are important in order to be lazy, and of course these two are also necessary.

2) Log in to mysql with root to execute the following code

/ / create a user dean password of 123456 and grant replication slave permission:

Mysql > grant replication slave on *. * to 'dean'@'192.189.0.201' identified by' 123456; www.2cto.com

/ / allow permissions to take effect immediately

Mysql > flush privileges

/ / query the file name and status of the binary file (to be used later)

Mysql > show master status\ G

File:mysql-bin.000006

Position:107

Binlog_Do_DB:

Binlog_Ignore_DB:

2. Slave operation:

1). Edit the myslq configuration file my.cnf:

[mysqld]

Server-id=2 / / as long as it is different from the host

2) Log in to mysql and enter the following command:

A), mysql > change master to master_host='192.168.0.200',master_user='dean',\

Master_password='123456',master_log_file='mysql-bin.000006'

Master_log_pos=107

B), mysql > start slave

C), mysql > show slave status\ G

If appears: Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Then there is no problem, you can build a database on the host to write test data, and then query from the computer to see if there is any data. There is usually no problem.

But if it appears:

Slave_IO_Running:Connecting

Slave_SQL_Running:Yes

Then it means that there is a problem with the master-slave service, and I have encountered this problem. Here is my solution:

1). Log in to the host mysql server directly from the slave:

# mysql-udean-h 192.168.0.200-p123456

If rejected, you need to check whether the permissions, firewalls and other settings of the host user dean are correct. (mine was rejected)

2), check the permission.

Log in to mysql with root and select the mysql library

Mysql > use mysql

Query the user's access to www.2cto.com

Mysql > select host,user from user

From the query data, there is no problem. Some dean users have received access to 192.168.0.201.

3), firewall settings

Set access to port 3306 to allow

# sudo ufw allow 3306

Set access with an ip of 192.168.0. 201 to allow

# sudo ufw allow 192.168.0.201

View statu

# sudo ufw status

After setting up, it is still a slave or cannot directly access the host remotely.

4) View port snooping

# netstat-anpy | grep 3306

It is found that the current port 3306 is only listening at 127.0.0.1, and the problem is found. Modify the mysql configuration file my.cnf to adjust the bind-address=127.0.0.1 comments in it, and re-check the port listening and find that the listening is 0.0.0.0 bind-address=127.0.0.1 3306. If you test it, you can really access it remotely.

Restart the operation of the master slave, but find that step 2 a cannot be done in the slave, execute the following command

Mysql > stop slave;// turn off slave

Mysql > reset slave;// reset slave

Continue with the an operation and continue the operation.

Finally, the master-slave replication of Mysql database is completed.

In addition: according to the online information, if the host already has data before building master-slave replication, it needs to be locked on the host:

Mysql > flush tables with read lock

Then package the data directory and copy it to the slave machine, and then unlock it.

Thank you for your reading, the above is the content of "master-slave replication usage of MySQL5.5 database". After the study of this article, I believe you have a deeper understanding of the master-slave replication usage of MySQL5.5 database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.