准备工作
主服务器 IP 192.168.2.225
从服务器 IP 192.168.2.168
Mysql主服务器
1、配置防火墙允许3306/tcp端口
[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
2、关闭selinux #selinux是Linux中的安全
[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Disabled
3、修改/etc/my.cnf配置文件
[root@localhost ~]# vim /etc/my.cnf
server-id = 1 #服务器的ID,必须唯一
log-bin=mysql_bin #开启二进制日志功能,名字可以随便起,最好有含义
binlog_cache_size=1M #为每个session分配的内存,在事务过程中用来存储二进制的缓存
binlog_format=mixed #主从复制的格式化(mixed,statement,row,默认格式是 statement)
expire_logs_days=7 #二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
slave_skip_errors=1062
#跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端服务终端。如:1062错误是指一些主键
重复,1032 错误是因为主从数据库数据不一致
log_slave_updates=1 log_slave_update 表示 slave 将复制事件写进自己的二进制日志
relay-log-index = slave-relay-bin.index #作为从服务器时的中继日志
auto-increment-increment = 2 # 自增因子(每次加2)
auto-increment-offset = 1 # 自增偏移(从1开始),单数
[root@localhost ~]# systemctl restart mysqld
4、修改mysql登录密码
[root@localhost ~]# mysqladmin -u root password '123'
You have new mail in /var/spool/mail/root
[root@localhost ~]# mysql -u root -p #登录mysql
5、给从服务器授权账号
mysql> grant replication slave on *.* to 'slave'@'192.168.2.%' identified by '123456'; #给从服务器授权账号
mysql> flush privileges; #刷新日志
mysql> show master status; #查看File列显示日志名,position列显示偏移量,这两个值在后面配置从服务器的时候需要。
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000002 | 507 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6、配置同步
mysql> change master to master_host='192.168.2.168',master_user='slave',master_password='123456',master_log_file='mysql_bin.000007',master_log_pos=585; #按从服务器结果更改上面命令中的master_log_file和master_log_pos参数
mysql> start slave; #启动slave服务
mysql> show slave status\G; #查看Slave状态,确保以下两个值为YES。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.03 sec)
mysql> create database lxy;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lxy |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use lxy;
mysql> create table user (id int(10) not null,name char(20) default '', primary key (id));
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+---------------+
| Tables_in_lxy |
+---------------+
| user |
+---------------+
1 row in set (0.01 sec)
mysql> insert into user values (1,'wo shi zhu server');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | wo shi zhu server |
+----+-------------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | wo shi zhu server |
| 2 | wo shi cong server |
+----+--------------------+
2 rows in set (0.00 sec)
Mysql从服务器
1、配置防火墙允许3306/tcp端口
[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
2、关闭selinux #selinux是Linux中的安全
[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Disabled
3、修改/etc/my.cnf配置文件
[root@localhost ~]# vim /etc/my.cnf
server-id = 2
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
binlog_format=mixed
binlog_cache_size=1M
expire_logs_days=7
slave_skip_errors=1062
log_slave_updates=1
auto_increment_increment=2 #ID自增从2开始,双数
auto_increment_offset=2
4、修改mysql登录密码
[root@localhost ~]# mysqladmin -u root -p password '123'
Enter password:
[root@localhost ~]# mysql -u root -p #登录mysql
5、配置同步
mysql> change master to master_host='192.168.2.225',master_user='slave',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=507; #按主服务器结果更改上面命令中的master_log_file和master_log_pos参数
mysql> start slave; #启动slave服务
mysql> show slave status\G; #查看Slave状态,确保以下两个值为YES。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6、给主服务器授权账号
mysql> grant replication slave on *.* to 'slave'@'192.168.2.%' identified by '123456'; #给主服务器授权账号
mysql> flush privileges; #刷新日志
mysql> show master status; #查看File列显示日志名,position列显示偏移量,这两个值在后面配置主服务器的时候需要。
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000007 | 585 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
7、验证主从服务器数据同步(包含同步1数据库、2表、3、记录:记录表示表中的数据)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lxy |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use lxy;
Database changed
mysql> show tables;
+---------------+
| Tables_in_lxy |
+---------------+
| user |
+---------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | wo shi zhu server |
+----+-------------------+
1 row in set (0.00 sec)
mysql> insert into user values (2,'wo shi cong server');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | wo shi zhu server |
| 2 | wo shi cong server |
+----+--------------------+
2 rows in set (0.00 sec)