MySQL master-slave replication configuration based on binlog

MySQL master-slave replication configuration based on binlog

The last time I configured the master-slave replication for MySQL was at the beginning of the year, I did not expect to start using MySQL again. If you don't talk much about it, go to the topic.

Master-Slave Build Environment:
Master:
OS: rhel-server-6.3-x86_64
Mysql: MySQL-5.5.35-1
Virtual Machine: Virtual BOX
Ip: 192.168.56.12

Slave:
OS: rhel-server-6.3-x86_64
Mysql: MySQL-5.5.35-1
Virtual Machine: Virtual BOX
Ip: 192.168.56.13

Overall steps:
1. Modify the Master configuration file (my. cnf)
2. Create a user with the copy permission on the Master.
3. Copy the database on the Master to Slave.
4. Modify the Slave configuration file (my. cnf)
5. Slave initialization and Replication
6. Test
7. FAQs

1. Modify the Master configuration file (my. cnf)

[Mysqld]
Server-id = 1 // the unique id of the server. The default value is 0,
Log-bin = mysql-bin // binary logs must be enabled
Replicate-do-db = zbdba

2. Create a user with the copy permission on the Master.

Mysql> create user zbdba;
Mysql> grant replication slave on *. * to zbdba identified by '20140901 ';

3. Copy the database on the Master to Slave.

Mysql> flush tables with read lock;
Mysql> show master status
->;
+ ----------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ----------------- + ---------- + -------------- + ------------------ +
| Ogg1-bin.000003 | 107 |
+ ----------------- + ---------- + -------------- + -------------------- + 1 row in set (0.00 sec)
Mysql> quit
[Root @ ogg/] #/usr/bin/mysqldump zbdba-uroot-pmysql -- opt | mysql zbdba-uroot-pmysql-h 192.168.56.13
Authorization in slave is required:
Grant all privileges on *. * TO 'root' @ '192. 168.56.13 'identified by 'mysql' with grant option;

Mysql> unlock tables;

4. Modify the Slave configuration file (my. cnf)

[Mysqld]
Server-id = 2 // the unique id of the server. The default value is 0,
Log-bin = mysql-bin // binary logs must be enabled
Replicate-do-db = zbdba

5. Slave initialization and Replication

Mysql> slave stop;
Mysql> change master to MASTER_HOST = '192. 168.56.12 ',
MASTER_USER = 'zbdb', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'ogg1-bin.000003 ', MASTER_LOG_POS = 123456;
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.12
Master_User: zbdba
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ogg1-bin.000003
Read_Master_Log_Pos: 107
Relay_Log_File: ogg2-relay-bin.000002
Relay_Log_Pos: 252
Relay_Master_Log_File: ogg1-bin.000003
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: zbdba

6. Test

Master:

Mysql> show tables;
+ ----------------- +
| Tables_in_zbdba |
+ ----------------- +
| Guestbook |
| Test |
| Test1 |
+ ----------------- +

Create table test2 select * from test1;

Mysql> show tables;
+ ----------------- +
| Tables_in_zbdba |
+ ----------------- +
| Guestbook |
| Test |
| Test1 |
| Test2 |
+ ----------------- +

Salve:

Mysql> show tables;
+ ----------------- +
| Tables_in_zbdba |
+ ----------------- +
| Guestbook |
| Test |
| Test1 |
| Test2 |
+ ----------------- +
4 rows in set (0.00 sec)

If you need to configure the relationship between master and slave, you only need to create the corresponding user on the slave database, and initialize the original master.

7. FAQs
There is no corresponding my. cnf file under/etc
Solution:
If mysql is installed using the rpm package, no corresponding my. cnf file. At this time, you only need to copy one/usr/share/mysql directory. cnf file to the/etc directory and renamed it my. cnf.

The Server-id parameter cannot be modified.
Solution:
Check whether the same server-id = 1 exists in my. cnf.

MySQL master/backup ERROR: ERROR 1201 (HY000 ):
Solution:
Mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql> reset slave;

Query OK, 0 rows affected, (0.00 sec)

ERROR 1130 (HY000): Host '*. *' is not allowed to connect to this MySQL server
Solution:
If you want to allow myuser to connect to the mysql server from a host whose ip address is 192.168.1.3, and use mypassword as the password
Grant all privileges on *. * TO 'myuser' @ '192. 168.1.3 'identified BY 'mypassword' with grant option;

MySQL uses backup and binlog for data recovery

Binlog section in binlog suppression by MySQL

MySQL Data Recovery-binlog

Clear binlog logs in MySQL

How to safely delete binlog logs under MySQL

MySQL -- binlog log data recovery

How does MySQL Delete binlog and restore Data?

Introduction and Analysis of Three MySQL binlog formats

MySQL uses binlog Incremental backup + restore instance

MySQL deletes binlog logs and restores data.

This article permanently updates the link address: