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

To build master-slave synchronization, you need to install MySQL on both computers. What I install here is CentOS6.7 64-bit, MySQL-5.5.33.

MySQL is installed in a binary package: http://aby028.blog.51cto.com/5371905/1892818

The operation of the two sets is the same, requiring to be able to log in to the mysql database.

The master and slave addresses are as follows:

Master:192.168.133.128

Slave:192.168.133.132

Ifconfig eth0 | grep "inet addr" | awk'{print $2}'| cut-d:-f2

Environment introduction:

Cat / etc/redhat-release

Uname-a

Configure the my.cnf file of the main library to filter out lines starting with #, $for ease of operation

Cat / etc/my.cnf | grep-v ^ # | grep-v ^ $> tmp.log

Cat tmp.log > / etc/my.cnf

Edit / etc/my.cnf file

Server-id = 1

Log-bin=mysql-bin

# max_binlog_size=500M

# binlog-do-db=test

# binlog-ignore-db=test1

Skip-name-resolve

Lower_case_table_names=1

The above parameters are described:

Server-id = 1 # indicates the server ID, which can be customized, is unique in the host and must be a number

Log-bin=mysql-bin # means to enable mysql binary logging, which must be started, otherwise the master will never take effect

# max_binlog_size=500M # means a maximum of 500m per binlog file, but one record will not be written in two logs, so the log will sometimes exceed 500m

# binlog-do-db=test # indicates the name of the database to be synchronized. If there are multiple databases, you can add one more line to it.

# binlog-ignore-db=test1 # indicates the name of a database that does not need to be synchronized. If there are multiple databases, you can add an extra line in it.

Skip-name-resolve # access by ignoring hostname

Lower_case_table_names=1 # ignores database table name case

If binlog-do-db=test and binlog-ignore-db=test1 are not written or annotated, it means to synchronize all databases. I am synchronizing all databases here.

You need to restart the mysql service after modifying the my.cnf file

/ etc/init.d/mysqld restart

Log in to the master library, create an account and authorize it for master-slave synchronization

Don't forget to refresh permissions after creating an account

Grant replication slave on *. * to yuci@'%' identified by '123456'

Flush privileges

Check what you just did.

Select user,host from mysql.user

Show grants for yuci@'%'

Lock the table. After locking the table, you can only read any information that cannot be created or inserted.

Flush table with read lock

View the file name of binlog and the pos location node

Show master status

Log in to the master database with the user you just created on the slave library, and test whether the two databases are connected

Mysql-uyuci-p123456-h292.168.133.128

Open a new remote interface, connect to the main library and export the database. (make sure that the previous mysql login interface does not exit)

After transferring the newly exported test.sql file to the slave library via scp

Mysqldump-uroot-p123456-- events-A-B > / root/test.sql

Scp test.sql root@192.168.133.132:/root

Yes

Go back to the main library and unlock it

Unlock tables

Import the test.sql file into the database on the slave library

Mysql-uroot-p123456 < / root/test.sql

Edit the my.cnf file from the library and change the server-id to 2

Vim / etc/my.cnf

Server-id = 2

The my.cnf file has been modified and mysql needs to be restarted

/ etc/init.d/mysqld restart

Log in to mysql to configure the synchronization parameters of the slave library

CHANGE MASTER TO

MASTER_HOST='192.168.133.128'

MASTER_PORT=3306

MASTER_USER='yuci'

MASTER_PASSWORD='123456'

MASTER_LOG_FILE='mysql-bin.000001'

MASTER_LOG_POS=107

Configuration of the above parameters

CHANGE MASTER TO

MASTER_HOST='192.168.133.128', # represents the IP of the main library

MASTER_PORT=3306, # indicates the port of the main library

MASTER_USER='yuci', # indicates the user on the main library that allows synchronization

MASTER_PASSWORD='123456', # indicates the password of the synchronized user

MASTER_LOG_FILE='mysql-bin.000001', # indicates which binlog file to start synchronization from

MASTER_LOG_POS=107; # indicates which pos node location of the binglong file starts synchronization

Perform start slave operation on slave library, IO connection

Start slave

To view the current status, the key points are as follows

Show slave status\ G

Key to check the following parameters

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes # network between master and slave

Slave_SQL_Running: Yes # data between master and slave

Seconds_Behind_Master: the number of seconds behind the master database in 0 # slave database

Create a database in the master library to test whether the master and slave are synchronized

Create database

View in the main library

Show databases

The master-slave synchronization is successful when viewed in the slave library

Show databases

Zhao

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.