Mysql master-slave replication configuration based on binlog _ MySQL

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. Not much said, enter the main question Master-Slave build environment: Master: OS: rhel-server-63-x86_64Mysql: MySQL-5535-1 virtual machine: VirtualBOX last configured mysql Master-Slave replication or at the beginning of the year, I didn't expect to start using mysql again now. If you don't talk much about it, go to the topic.

Master-Slave build environment: Master: OS: rhel-server-6.3-x86_64Mysql: MySQL-5.5.35-1 Virtual Machine: Virtual BOXIp: 192.168.56.12Slave: OS: rhel-server-6.3-x86_64Mysql: MySQL-5.5.35-1 Virtual Machine: Virtual BOXIp: 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 // it must be the unique id of the server. the default value is 0. log-bin = mysql-bin // binary log replicate-do-db = zbdba must be enabled.

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

mysql>create user zbdba; mysql>grant replication slave on *.* to zbdba identified by '123456';

3. copy the database on the Master to Slave.

Mysql> flush tables with read lock; mysql> show master status->; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | ogg1-bin.000003 | 107 | + usage + -------- + -------------- + ------------------ + 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 must be authorized in slave: 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 // it must be the unique id of the server. the default value is 0. log-bin = mysql-bin // binary log replicate-do-db = zbdba must be enabled.

5. Slave initialization and replication

mysql>slave stop; mysql>CHANGE MASTER TO MASTER_HOST='192.168.56.12',MASTER_USER='zbdba',MASTER_PASSWORD='123456',MASTER_LOG_FILE='ogg1-bin.000003',MASTER_LOG_POS=107;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;