首页 / MYSQL / mysql主从复制配置过程及演示
mysql主从复制配置过程及演示
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql主从复制配置过程及演示,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7885字,纯文字阅读大概需要12分钟。
内容图文
![mysql主从复制配置过程及演示](/upload/InfoBanner/zyjiaocheng/483/5f9ccdedfa994a8d9e8e532ed200e808.jpg)
编辑/data/3306/my.cnf:
[mysqld]
server-id = 6 #主库和从库的server-id不能相同
log_bin = /data/3306/mysql-bin #
重启服务
/data/3306/mysql restart
检查思路1:
[root@db02 data]# egrep "log_bin|server-id" 330*/my.cnf
3306/my.cnf:log_bin = /data/3306/mysql-bin
3306/my.cnf:server-id = 6
3307/my.cnf:server-id = 7
检查思路2:
登录:
[root@db02 data]# mysql -S /data/3306/mysql.sock
查看变量:
mysql> show variables like ‘log_bin%‘;#查看主库的binlog是否存在
+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| log_bin | ON |
| log_bin_basename | /data/3306/mysql-bin |
| log_bin_index | /data/3306/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+----------------------------+
5 rows in set (0.00 sec)
查看原主库数据库中原有库
[root@mysql-db02 data]# mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cc2 |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql>
查看从库中原有库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cc |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql>
2、建账号授权【主库】
grant replication slave on *.* to ‘rep‘@‘172.16.1.%‘ identified by ‘cc123‘;
flush privileges;
3、锁表导出数据
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
查看位置:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 405 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
新开窗口备份:
mysqldump -uroot -p‘cc123‘ -S /data/3306/mysql.sock -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
原窗口解锁:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 405 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
如下命令可替代3的所有步骤
mysqldump -uroot -p‘cc123‘ --master-data=2 -S /data/3306/mysql.sock -A -B
zcat mysql_bak.2017-05-04.sql.gz >mysql_bak.2017-05-04.sql
4、将数据导入到从库
[root@db02 backup]# mysql -S /data/3307/mysql.sock <mysql_bak.2017-05-04.sql
5、让从库从主库锁表时刻记录的binlog位置点开始向下同步
CHANGE MASTER TO
MASTER_HOST=‘172.16.1.52‘,
MASTER_PORT=3306,
MASTER_USER=‘rep‘,
MASTER_PASSWORD=‘cc123‘,
MASTER_LOG_FILE=‘mysql-bin.000028‘,
MASTER_LOG_POS=1728;
#GTID
这时可见master.info已经产生在从库/data 目录下
[root@mysql-db02 data]# cd /data/3307/data/
[root@mysql-db02 data]# ls
auto.cnf cc2 ib_logfile1 mysql mysql-bin.000003 mysql-bin.000006 mysql-bin.000009 mysql-bin.000012 mysql-bin.000015 performance_schema
binlog.sql ibdata1 ib_logfile2 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 mysql-bin.000010 mysql-bin.000013 mysql-bin.000016 test
cc ib_logfile0 master.info mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 mysql-bin.000011 mysql-bin.000014 mysql-bin.index
[root@mysql-db02 data]#
6、启动同步开关
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
7、检查
[root@db02 backup]# mysql -S /data/3307/mysql.sock -e "show slave st
atus\G"|egrep -i "Yes|Behind_Master"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
检查3307里是否同步主库
[root@mysql-db02 data]# mysql -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cc |
| cc2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql>
在cc2库下student表中插入内容
这时我们在往3306里插入内容就会发现已经同步过来了
[root@mysql-db02 data]# mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> use cc2;
mysql> insert into student(`id`,`name`) VALUES(8,‘bb‘);
mysql> insert into student(`id`,`name`) VALUES(8,‘bb‘);
mysql> insert into student(`id`,`name`) VALUES(8,‘bb‘);
mysql> select * from student;
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
| 5 | DD | 0 | NULL |
| 6 | cc | 0 | NULL |
| 7 | bb | 0 | NULL |
| 8 | bb | 0 | NULL |
| 8 | bb | 0 | NULL |
| 8 | bb | 0 | NULL |
| 8 | bb | 0 | NULL |
+----+------+-----+------+
7 rows in set (0.00 sec)
[root@mysql-db02 data]# mysql -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> use cc2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from student;
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
| 5 | DD | 0 | NULL |
| 6 | cc | 0 | NULL |
| 7 | bb | 0 | NULL |
| 8 | bb | 0 | NULL |
| 8 | bb | 0 | NULL |
| 8 | bb | 0 | NULL |
+----+------+-----+------+
6 rows in set (0.00 sec)
mysql>
本文出自 “cc” 博客,谢绝转载!
mysql主从复制配置过程及演示
标签:mysql
本文系统来源:http://ccokay.blog.51cto.com/11714322/1929483
内容总结
以上是互联网集市为您收集整理的mysql主从复制配置过程及演示全部内容,希望文章能够帮你解决mysql主从复制配置过程及演示所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。