MySQL based Binlog master-slave replication configuration

The last time I configured MySQL for master-slave replication or the beginning of the year, I did not expect to start using MySQL now. Words don't say much, get to the point

Master-slave setting up the Environment: master:os:rhel-server-6.3-x86_64mysql:mysql-5.5.35-1 virtual machine: Vsan Boxip:192.168.56.12slave:os: Rhel-server-6.3-x86_64mysql:mysql-5.5.35-1 virtual machine: Vsan boxip:192.168.56.13

Overall steps:
1. Modify master configuration file (my.cnf)
2. Create a user with copy permission on Master
3. Copy the database on master to slave
4. Modify the slave configuration file (my.cnf)
5. Slave initialization replication
6. Testing
7. Frequently Asked questions

1. Modify master configuration file (my.cnf)

[Mysqld] server-id=1  //Must be a server unique ID, default is 0,  log-bin=mysql-bin//must enable binary logging Replicate-do-db=zbdba

2. Create a user with copy permission on Master

Mysql>create user zbdba; Mysql>grant replication Slave on * * to ZBDBA identified by ' 123456 ';

3. Copy the database on 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[[email Protected]/]#/usr/bin/mysqldump zbdba-uroot-pmysql--opt |  MySQL zbdba-uroot-pmysql-h 192.168.56.13 need to be licensed 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  //Must be a server unique ID, default is 0,  log-bin=mysql-bin//must enable binary logging Replicate-do-db=zbdba

5. Slave initializing 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, only need to set up the corresponding user on the slave database, the original master to do the initialization on the line.

7. Frequently Asked questions
/etc does not have a corresponding my.cnf file
Workaround:
If MySQL is installed with the RPM package, and/etc does not have a corresponding my.cnf file, this time only needs to copy a. cnf file from the/usr/share/mysql directory to/etc directory, and renamed to MY.CNF.


Server-id parameter cannot be modified
Workaround:
See if there is the same server-id=1 in MY.CNF


This error is present in MySQL Master: Error 1201 (HY000):
Workaround:
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 ' *.*.*.* ' isn't allowed to connect to this MySQL server
Workaround:
If you want to allow users to connect to the MySQL server from a host myuser IP 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 based Binlog master-slave replication configuration