MySQL 5.7 主从复制(主从同步)
1、说明：IP 计算机名 角色
192.168.1.101 MySQL-001master
192.168.1.102 MySQL-002slave
系统：CentOS 6. 或 7.
MySQL版本：5.7
mysql安装步骤链接
http://blog.51cto.com/10158955/1926574
注意！根据自己的安装路径不同和数据目录不同而修改！（包括配置文件）
2、master配置文件设置如下
一般mysql配置文件在/etc/my.cnf
(如果找不到的话也有可能在这些目录下：/etc/mysql/my.cnf，/usr/local/mysql/etc/my.cnf，~/.my.cnf)
# vim /etc/my.cnf
basedir=/usr/local/mysql
datadir=/data/mysqldata
socket=/tmp/mysql.sock
user=mysql
port=3306
master的配置
server-id=1 # 服务器id (主从必须不一样)
binlog-do-db=employees# 要给从机同步的库
binlog-ignore-db=mysql# 不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
log-bin=mysql-bin # 打开日志(主机需要打开)，这个mysql-bin也可以自定义；
expire_logs_days=90 # 自动清理 90 天前的log文件,可根据需要修改
重启数据库使配置生效：
CentOS 6.：
# service mysqld restart
# service mysqld status
SUCCESS! MySQL running (15607)
CentOS 7.：
# systemctl restartmysqld.service
# systemctl status mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 29959 (mysqld)
CGroup: /system.slice/mysqld.service
└─29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
5月 17 11:42:02 tcloud-118 systemd: Started MySQL Server.
测试log_bin是否成功开启
# mysql -uroot -p
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| log_bin | ON |
| log_bin_basename | /opt/mysql/logs/mysql-bin |
| log_bin_index | /opt/mysql/logs/mysql-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------+
6 rows in set (0.00 sec)
mysql>
可以看到log_bin为ON；
3、master的数据库中建立主从同步账号backup：
backup为用户名，192.168.1.%表示只允许192.168.1网段的客户端连接，123456为密码；
mysql> grant replication slave on . to 'backup'@'192.168.1.%'> mysql> flush privileges;
mysql> select Host,User,authentication_string from mysql.user;
+--------------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+--------------+---------------+-------------------------------------------+
| localhost | root | 6C362347EBEAA7DF44F6D34884615A35095E80EB |
| localhost | mysql.session | THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| 192.168.1.%| backup | 9BB58B7F11A03B83C396FF506F3DF45727E79614 |
+--------------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>backup@192.168.1.%%E8%B4%A6%E6%88%B7%E5%B7%B2%E5%BB%BA%E7%AB%8B%EF%BC%9B
页:
[1]