Mysql's master-slave replication

First, the database master-slave replication Basic requirements:

1、操作系统版本一致（建议）。2、数据库版本一致（建议）。3、配置文件参数一致（是指一些性能参数）。

Second, network topology

III. Primary Database configuration file

[mysqld]user = mysqlbasedir = /app/mysqldatadir = /app/mysql/dataport=3306server-id = 10                             #要与从库区别出来log-bin = mysql-bin                  #必须启用binlog日志功能，主从基于日志同步binlog_format = mixed             #binlog日志格式expire_logs_days = 7               #slave_skip_errors=1028

Iv. authorizing replication from a library user on the main library

mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> grant replication slave on *.* to ‘mysync‘@‘10.3.150.199‘ identified by ‘123456‘;Query OK, 0 rows affected, 1 warning (0.04 sec)

V. Back up the test database of the main library

[[email protected] ~]# mysqldump -uroot -F -B test --single-transaction --master-data --triggers --routines --events  > test.sql#--master-data 默认参数值是1，mysqldump出来的文件就会包括创：建数据库语句和CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，file和position记录的位置就是slave从master端复制文件的起始位置[[email protected] ~]# ll总用量 12-rw-------. 1 root root  959 6月  17 15:20 anaconda-ks.cfgdrwxr-xr-x  2 root root 4096 8月  13 06:57 backup-rw-r--r--  1 root root 2254 8月  26 02:21 test.sql     #数据库备份文件[[email protected] ~]# scp test.sql [email protected]:/root/          #把此文件复制到从库里

Vi. Modifying a configuration file from a library

[mysqld]user = mysqlbasedir = /app/mysqldatadir = /app/mysql/dataport=3306server-id = 22          #从库必须要与主库不一致

Vii. setting up a synchronization account on the Create from library

mysql> change master to    -> master_host=‘10.3.150.198‘,    -> master_user=‘mysync‘,    -> master_password=‘123456‘;Query OK, 0 rows affected, 2 warnings (0.08 sec)

Eight, import the main library back up the Test.sql file

    mysql> source /root/test.sql;    mysql> show databases;    +--------------------+    | Database           |    +--------------------+    | information_schema |    | mysql              |    | performance_schema |    | sys                |    | test               |    +--------------------+    5 rows in set (0.00 sec)

Nine, start master-Slave synchronization

mysql> start slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.3.150.198                  Master_User: mysync                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000011          Read_Master_Log_Pos: 154               Relay_Log_File: web02-relay-bin.000007                Relay_Log_Pos: 367        Relay_Master_Log_File: mysql-bin.000011             Slave_IO_Running: Yes           #代表同步成功            Slave_SQL_Running: Yes         #代表同步成功

Nine, about the master-slave synchronization of some exception handling methods 1, hint error: last_io_error:fatal error:the slave I/O thread stops because master and slave have equal MySQL Server UUIDs; These uuids must is different for replication to work. "

The main reason: the master-slave server is cloned, resulting in two MySQL auto.cnf files in the Server-uuid value is identical.

2. Prompt error:

Mysql's master-slave replication