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

This article mainly introduces the mysql database AB replication how to configure, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Use two mysql servers to achieve AB, master-slave replication.

First, configure in the main MASTER server

MASTER 172.16.1.3

BACKUP 172.16.1.2

1. Edit the my.cnf file

# add these two lines to the original

[root@zhaoyun ~] # cat / etc/my.cnf

[mysqld]

Log-bin=/mysql/bin # enable binlog

Server-id=1 # configuration should not be duplicated with another one.

2. Restart the service

[root@zhaoyun ~] # service mysqld restart

Stop MySQL: [OK]

Start MySQL: [OK]

3. Authorized users

Mysql > grant replication slave on *. * to zhaoyun@172.16.1.2 identified by '123456'

[root@zhaoyun] # mysql-uroot-p123456

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

Your MySQL connection id is 2

Server version: 5.0.77-log Source distribution

Type 'help;' or'\ h' for help. Type'\ c'to clear the buffer.

Mysql >

Mysql > grant replication slave on *. * to zhaoyun@172.16.1.2 identified by '123456'

Query OK, 0 rows affected (0.15 sec)

Mysql >

4. Test whether you can log in on the B server

[root@BACKUP] # mysql-uzhaoyun-p123456-h272.16.1.3

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

Your MySQL connection id is 4

Server version: 5.0.77-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software

And you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show grants

+-+

| | Grants for zhaoyun@172.16.1.2 |

+-+

| | GRANT REPLICATION SLAVE ON *. * TO 'zhaoyun'@'172.16.1.2' IDENTIFIED BY PASSWORD' 565491d704013245' |

+-+

1 row in set (0.00 sec)

Mysql >

5. Check the status of master

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | bin.000001 | 315 |

+-+

1 row in set (0.00 sec)

The file field is the binlog file name of master, and position is the node of binlog.

2. Configure BACKUP

1. Edit the configuration file my.cnf and add 4 lines.

[mysqld]

Server-id=2

Master-host=172.16.1.3 # ip address of the MASTER server

Master-user=zhaoyun # user name to connect to the MASTER server

Master-password=123456 # password

2. Restart the service

[root@BACKUP ~] # service mysqld restart

Stopping mysqld: [OK]

Starting mysqld: [OK]

3. After restarting the service, several files will be generated in the directory.

[root@BACKUP] # ls

Ib_logfile1 mysqld-relay-bin.000001 mysqld-relay-bin.index test

Ibdata1 master.info mysql.sock

Ib_logfile0 mysql relay-log.info

[root@BACKUP mysql] # pwd

/ var/lib/mysql

Mysqld-relay-bin.000001 # binload file, copied from master

Information about mysqld-relay-bin.index # binload

Master.info # master Information

Relay-log.info # Relay Log Information

4. Check the status of slave

[root@BACKUP] # mysql-uroot-p123456

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

Your MySQL connection id is 5

Server version: 5.0.77 Source distribution

Type 'help;' or'\ h' for help. Type'\ c'to clear the buffer.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.1.3

Master_User: zhaoyun

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000001

Read_Master_Log_Pos: 315

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 453

Relay_Master_Log_File: mysqld-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 315

Relay_Log_Space: 453

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

# this is the status of the binlog file of the main service. If IO is NO, you need to check the status of these two files.

Master_Log_File: mysqld-bin.000001

Read_Master_Log_Pos: 315

# these are the IO processes of slave and the status of SQL processes. Services copied by AB are available only if they are both yes.

Slave_IO_Running: YES

Slave_SQL_Running: YES

# if the IO process is NO, you can delete the data files of BACKUP. Restart the service and resynchronize it.

5. Up to now, the configuration is basically complete.

Third, create a table to test whether the synchronization is successful.

1. Create it on master.

Mysql > create database master

Query OK, 1 row affected (0.00 sec)

Mysql > use master

Database changed

Mysql > create table master (id int,name char (5))

Query OK, 0 rows affected (0.04 se

2. Check in backup

[root@BACKUP] # mysql-uroot-p123456

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

Your MySQL connection id is 6

Server version: 5.0.77 Source distribution

Type 'help;' or'\ h' for help. Type'\ c'to clear the buffer.

Mysql > show database

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | master |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > use master

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_master |

+-+

| | master |

+-+

1 row in set (0.00 sec)

Mysql >

You can see that the data has been synchronized. So far the experiment has been successful.

Troubleshooting:

IO equals NO: you need to check whether the node and binlog file names are the same as those seen in master. If not, you can rewrite them manually.

Command

Stop the slave service first

Mysql > slave stop

Mysql > change master to master_log_file= "binlog file name seen in master"

The number mysql > change master to master_log_pos=100; is seen in master.

Mysql > slave start

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysqld-bin.000001 | 507 |

+-+

1 row in set (0.00 sec)

SQL equals NO. You can try to delete several files and restart the service to resynchronize.

Mysqld-relay-bin.000001 # binload file, copied from master

Information about mysqld-relay-bin.index # binload

Master.info # master Information

Relay-log.info # Relay Log Information

Thank you for reading this article carefully. I hope the article "how to configure mysql database AB replication" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.