以前的文章有写过mysql的配置、MySQL数据库的多实例，此次配置mysql的主从同步：
一、主库开启BINLOG、server-id
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[root@Master-Mysql ~]# grep -E "server-id|log-bin" /etc/my.cnf log-bin = /usr/local/mysql/data/mysql-bin server-id = 1 mysql> show variables like '%log_bin%'; +---------------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------------+ 6 rows in set (0.01 sec) mysql> show variables like '%server_id%'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | server_id | 1 | | server_id_bits | 32 | +----------------+-------+ 2 rows in set (0.00 sec) |
备注：以上两个信息必须在[mysqld]模块下！！！文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/configuration-mysql-master-slave/
二、给从库授权
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> grant replication slave on *.* to byrd@'192.168.199.%' identified by 'admin'; mysql> flush privileges; mysql> select user,host from mysql.user; +------+---------------+ | user | host | +------+---------------+ | root | 127.0.0.1 | | byrd | 192.168.199.% | | root | ::1 | | root | lamp | | root | localhost | +------+---------------+ 5 rows in set (0.00 sec) |
锁表前建立点数据：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> create database hitest; mysql> show databases; +--------------------+ | Database | +--------------------+ | hitest | +--------------------+ 6 rows in set (0.00 sec) mysql> use hitest; mysql> create table test( -> id int(4) not null primary key auto_increment, -> name char(20) not null -> ); Query OK, 0 rows affected (1.80 sec) mysql> show tables ; +------------------+ | Tables_in_hitest | +------------------+ | test | +------------------+ mysql> insert into test(id,name) values(1,'zy'); mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | zy | +----+------+ |
三、锁表、备份、解锁
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
mysql> flush table with read lock; #锁表 mysql> show variables like '%timeout%'; #锁表时间 +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | interactive_timeout | 28800 | | wait_timeout | 28800 | +-----------------------------+----------+ 12 rows in set (0.06 sec) mysql> show master status; #binlog日志位置 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 1305 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.03 sec) [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'' -B -A |gzip >/tmp/all.sql.gz #新窗口备份 Enter password: mysql> unlock table; #解锁 ###############解锁后主库操作如下：############### mysql> use hitest mysql> insert into test(id,name) values(2,'binghe'); mysql> select * from test; +----+--------+ | id | name | +----+--------+ | 1 | zy | | 2 | binghe | +----+--------+ mysql> create database hxy; ###############解锁后主库操作完成~############### |
备注：备份数据需要重新打开新窗口，不然锁表就自动失效；文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/configuration-mysql-master-slave/
四、主库导入到从库
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
################主库操作################ [root@Master-Mysql tmp]# ll -rw-r--r--. 1 root root 162236 Jul 8 21:30 all.sql.gz [root@Master-Mysql tmp]# gzip -d all.sql.gz [root@Master-Mysql tmp]# ll -rw-r--r--. 1 root root 590351 Jul 8 21:30 all.sql ################主库完成################ ##备注：将主库导出的all.sql通过scp、ssh、sftp等方式拷贝到从库服务器，此处略## [root@Slave-Mysql ~]# grep log-bin /etc/my.cnf #log-bin = /usr/local/mysql/data/mysql-bin [root@Slave-Mysql ~]# grep server-id /etc/my.cnf server-id = 2 [root@Slave-Mysql ~]# /etc/init.d/mysqld restart [root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin' </tmp/all.sql Warning: Using a password on the command line interface can be insecure. [root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin' mysql> use hitest; mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | zy | +----+------+ 1 row in set (0.00 sec) |
五、从库配置信息
|
0 1 2 3 4 5 6 7 8 9 |
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.199.177', -> MASTER_PORT=3306, -> MASTER_USER='byrd', -> MASTER_PASSWORD='admin', -> MASTER_LOG_FILE='mysql-bin.000004', -> MASTER_LOG_POS=1305; Query OK, 0 rows affected, 2 warnings (1.96 sec) [root@Slave-Mysql ~]# ll /usr/local/mysql/data/master.info ##备注：master.info记录MASTER的相关信息！ |
|
0 1 2 3 4 5 6 7 8 |
cat |/usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock <<EOF CHANGE MASTER TO MASTER_HOST='192.168.199.177', MASTER_PORT=3306, MASTER_USER='byrd', MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1305; EOF |
六、启动从库同步
|
0 1 2 3 4 |
mysql> start slave; mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 |
七、结果测试
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> use hitest; mysql> select * from test; +----+--------+ | id | name | +----+--------+ | 1 | zy | | 2 | binghe | +----+--------+ 2 rows in set (0.00 sec) [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "create database zhihu;" #主库建立了一个zhihu的数据库 Enter password: [root@Slave-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "show databases like 'zhihu'"; Enter password: +------------------+ | Database (zhihu) | +------------------+ | zhihu | +------------------+ |
|
0 1 2 |
mysql> grant replication slave on web.* to rep@'1.1.1.11' identified by 'admin'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES 不允许这样操作，可以在my.cnf中使用参数跳过不需要同步的库 |
八、状态查询
主库：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> show processlist\G *************************** 1. row *************************** Id: 135 User: byrd Host: 103.11.11.11:41910 db: NULL Command: Binlog Dump Time: 671052 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 21664 User: byrd Host: 12.12.12.12:59274 db: NULL Command: Binlog Dump Time: 414 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 3. row *************************** Id: 21665 User: root Host: localhost db: NULL Command: Query Time: 0 State: init Info: show processlist 3 rows in set (0.00 sec) |
从库：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 111.111.111.110 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 17310928 Relay_Log_File: Backup-relay-bin.000002 Relay_Log_Pos: 43256 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: web Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 17310928 Relay_Log_Space: 43472 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: 3c5ddbba-0a4f-11e6-9615-00163e0046cb 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) |
申明：本文由BYRD原创(基于Mysql5.6.16)，未经许可禁止转载！
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
Trackbacks