MySQL复制简要描述及示例 - 数据库编程

******* Level: Note Code: 1760 Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives. 2 rows in set (0.00 sec) #此时查看从库的状态信息 slave@localhost[(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.177 Master_User: repl Master_Port: 3406 Connect_Retry: 60 Master_Log_File: inst3406bin.000001 Read_Master_Log_Pos: 4 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: inst3406bin.000001 Slave_IO_Running: No #IO线程没有运行 Slave_SQL_Running: No #SQL线程没有运行 ...................... Master_Info_File: /data/inst3506/data3506/master.info slave@localhost[(none)]> start slave; #启动slave Query OK, 0 rows affected (0.01 sec) #含义如下 START SLAVE with no thread_type options starts both of the slave threads. The I/O thread reads events from the master server and stores them in the relay log. The SQL thread reads events from the relay log and executes them. #再次查看slave的状态 robin@localhost[(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.177 Master_User: repl Master_Port: 3406 Connect_Retry: 60 Master_Log_File: inst3406bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 285 Relay_Master_Log_File: inst3406bin.000001 Slave_IO_Running: Yes #IO线程处于运行状态 Slave_SQL_Running: Yes #SQL线程处于运行状态 .............. Exec_Master_Log_Pos: 120 Relay_Log_Space: 452 ............ Master_Server_Id: 3406 Master_UUID: 32f53a0a-63ef-11e4-93d9-8c89a5d108ae Master_Info_File: /data/inst3506/data3506/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 #重要的提示信息 #可以看到从库上的2个线程，一个是用于I/O线程，用于连接到主库请求主库发送binlog，一个是用于执行SQL的SQL线程。 slave@localhost[(none)]>

show processlist\G *************************** 1. row *************************** Id: 4 User: system user Host: db: NULL Command: Connect Time: 510993 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 5 User: system user Host: db: NULL Command: Connect Time: 333943 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL

5、验证同步情况

#下面在主库上执行一些操作以检查从库的同步情况
master@localhost[(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3406  |
+---------------+-------+
1 row in set (0.00 sec)

#主库上Binlog Dump线程用于发送binlog日志文件到从库，如下查询
master@localhost[(none)]> show processlist\G
*************************** 1. row ***************************
     Id: 12
   User: repl
   Host: 192.168.1.177:57440
     db: NULL
Command: Binlog Dump
   Time: 511342
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
   
#主库创建数据库及表
master@localhost[(none)]> create database tempdb;
Query OK, 1 row affected (0.01 sec)

master@localhost[(none)]> use tempdb
Database changed
master@localhost[tempdb]> create table tb_engines as select * from information_schema.engines;
Q

MySQL复制简要描述及示例(二)