mysql5.6主从配置
来源:互联网 发布:小智代练淘宝店 编辑:程序博客网 时间:2024/05/16 23:49
操作系统 :CentOS 6.7 数据库版本:MySQL 5.6.19主机A:192.168.0.104 (Master)主机B:192.168.0.102 (Slave)
前提:
源码编译安装的mysql数据库,保证数据库能跑通
主库操作:
MASTER主服务器文件配置:
vi /etc/my.cnf# log_binlog-bin=mysql-binserver-id=2binlog-ignore-db=information_schemabinlog-ignore-db=clusterbinlog-ignore-db=mysqlbinlog-do-db=test_master然后wq进行修改保存
service mysqld restart #重启服务
#1.登陆mysql -uroot -p#2.创建用户并分配权限(只有从服务器复制权限)GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.0.%' IDENTIFIED BY '123456'#创建了mysync用户 密码123456 196.168.0.% 表示只能在ip为192.168.0.0-255 进行远程登陆#3.获取主库上binlog的位置show master status+------------------+----------+--------------+----------------------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+----------------------------------+-------------------+| mysql-bin.000001 | 120 | test_master | information_schema,cluster,mysql | |+------------------+----------+--------------+----------------------------------+-------------------+#记住显示的 File | Position 的值#我此次的 file名是:mysql-bin.000001,Position值为:120
导出主服务器Mysql库中的数据
1. 导出主服务器上test数据库的数据
mysqldump -uroot -p test_master>/test_master.txt#输入密码
2. 将导出的数据库文件传递到从库所在的服务器,并导入从库的数据库
scp /test_master.txt root@192.168.0.102:/#将主库的数据文件传到从库服务器上
从库设置
设置从库对应的主库配置
vi /etc/my.cnflog-bin=mysql-binserver-id=3binlog-ignore-db=information_schemabinlog-ignore-db=clusterbinlog-ignore-db=mysqlreplicate-do-db=test_masterreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allslave-net-timeout=60
重启mysql
service mysqld restart
mysql -uroot -p
#停止slavemysql> stop slave;mysql> change master to master_host='192.168.0.104',master_user='mysync',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=256;#开启slavemysql>start slave;#查看配置的信息:mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.104 Master_User: mysync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 256 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test_master Replicate_Ignore_DB: mysql 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: 256 Relay_Log_Space: 460 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: 0Master_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: 2 Master_UUID: 322142ff-4904-11e7-b451-000c29fb678a 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: 01 row in set (0.00 sec)ERROR: No query specifiedmysql>
如果Slave_IO_Running 和 Slave_SQL_Running都为 Yes,而且没有出现error的字样,并验证Master_%等相关配置是否正确
然后就可以进行测试了。
阅读全文
0 0
- mysql5.6主从配置
- MySQL5.6X 主从配置
- mysql5.6配置主从数据库
- Mysql5.6主从热备配置
- win7下MYSQL5.6主从配置
- MySQL5.6 Replication主从配置教程
- centos 7 mysql5.6 主从配置
- Mysql5.6主从热备配置
- mysql5.6主从配置和数据库安装
- mysql5.6 主从复制的配置
- Centos7 + mysql5.6主从配置搭建
- Mysql5主从同步配置
- mysql5.5主从配置
- mysql5.5主从配置
- MySQL5.5主从配置
- mysql5.7.17主从配置
- MySQL5.7主从配置
- mysql5.7 主从配置
- Trafodion SQL调优基本步骤
- CentOS7 增加tomcat 启动,停止,使用systemctl进行配置
- 组合数计算,防止溢出
- 使用IntelliJ IDEA开发SpringMVC网站(一)开发环境
- 程序员穿越 90年代
- mysql5.6主从配置
- React-Native PanResponder的学习与使用
- 合并两个git仓库
- Android 友盟应用
- 第四章 创建一个社交网站
- MediaEncoder H264编码资料
- Deepgreen DB 是什么?
- .NET-数据拖动
- 项目中的动画效果,gif实现