mysql单主单从备份_mysql主从复制(单主到多从)

mysql> change master to master_host='192.168.56.87',master_user='test',master_password='123

',

master_log_file='mysql-bin.000005

',master_log_pos=120;

master_host      主机 IP

master_user      上面创建的复制用户名

master_passowrd  用户密码

master_log_file  从那个二进制文件开始复制        对应上面的filemaster_log_pos   从二进制文件的哪个位置开始复制    对应上面的position

mysql> start slave

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository 竟然报错了mysql> reset slave;   重置slave的参照

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)  现在才成功

mysql> show slave status\G;

启动Slave服务器，进入mysql控制台，查看同步状态。

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.87

Master_User: test

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 586

Relay_Log_File: mysql-relay-bin.000009

Relay_Log_Pos: 749

Relay_Master_Log_File: mysql-bin.000005

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: 586

Relay_Log_Space: 1294

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 84b3bb06-f8a9-11e4-9dad-080027ecb38e

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

正常状态下，看到上面的两行加粗处，即Slave_IO_Running和Slave_SQL_Running的参数都为 Yes，就说明主从同步运行正常。

9. 查看pet1 的数据有没有同步过来

mysql>  select * from pet1;

+----------+-------+---------+------+------------+-------+

| name     | owner| species  | sex  | birth      | death |

+----------+-------+---------+------+------------+-------+

| Puffball | Diane| hamster  | f    | 1999-03-30 | NULL  |

+----------+-------+---------+------+------------+-------+

pet1 也被同步过来了；

10、同步测试

10.1 在master上写入数据：

mysql> CREATE TABLE pet3 (name VARCHAR(20), owner VARCHAR(20),

species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

mysql>  INSERT INTO pet3  VALUES ('Puffball','Diane','hamster','f','1999-03-30

',NULL);             mysql> commit;       10.2 在slave上查看结果：  mysql> use test Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | pet            | | pet1           | | pet3           | +----------------+ 3 rows in set (0.00 sec) mysql> select * from pet3; +----------+-------+---------+------+------------+-------+ | name     | owner | species | sex  | birth      | death | +----------+-------+---------+------+------------+-------+ | Puffball | Diane | hamster | f    | 1999-03-30 | NULL  | +----------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec)  可以看到 pet3表被同步过来啦！