+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.03 sec)
结果显示没启动任何线程
2.8、配置从服务器连接主服务器
mysql> help change master to //查看命令
option:
MASTER_BIND = 'interface_name' //设置连接接口
| MASTER_HOST = 'host_name' //指定主节点名称
| MASTER_USER = 'user_name' //指定具有复制权限的用户（刚才创建的）
| MASTER_PASSWORD = 'password' //具有复制权限的用户的密码
| MASTER_PORT = port_num //主服务所监听的端口，默认为3306的话可以省略
| MASTER_CONNECT_RETRY = interval //无法连接主服务器的时候可以重试连接多少次
| MASTER_HEARTBEAT_PERIOD = interval //多长时间探测一次主服务器是否在线
| MASTER_LOG_FILE = 'master_log_name' //从哪个二进制日志中读取事件
| MASTER_LOG_POS = master_log_pos //指定哪个二进制日志中事件的位置
| RELAY_LOG_FILE = 'relay_log_name' //读取哪个中继日志
| RELAY_LOG_POS = relay_log_pos //中继日志中的位置
| MASTER_SSL = {0|1} //是否启动ssl功能
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
2.9配置从服务器连接主服务器
mysql> CHANGE MASTER TO MASTER_HOST='172.16.6.10', MASTER_USER='reuser', MASTER_PASSWORD='repass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;
2.10启动线程
1 mysql> start slave;
2.11查看从服务器工作状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event //I/O状态
Master_Host: 172.16.6.10 //主服务器ip
Master_User: reuser //具有复制权限的用户
Master_Port: 3306 //默认监听的端口
Connect_Retry: 60 //重试时间
Master_Log_File: mysql-bin.000003 //读取的二进制日志文件
Read_Master_Log_Pos: 435 //二进制日志中的位置
Relay_Log_File: relay-bin.000002 //当前读取的中继日志文件
Relay_Log_Pos: 581 //中继日志中事件的位置
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes //I/O线程已启动
Slave_SQL_Running: Yes //SQL线程已启动
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: 435
Relay_Log_Space: 731
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
1 row in set (0.07 sec)
2.12测试
在主服务器上创建数据库
[root@node1 data]# mysql
mysql> create database mydb;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test