1、安装mysql
# 可能需要更新系统包
sudo apt update
# 安装mysql服务端
sudo apt install mysql-server -y
2、创建mysql用户(如果不需要主从可以跳过此步骤)
# 创建用来主从复制的账号
# slave为用户名,%为可访问的ip地址,不限制可设为%
# mysql_native_password为密码的加密方式
# 如果使用默认的加密方式可以去掉 WITH mysql_native_password
# 123456为该用户的登录密码
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 给该用户授权可访问的数据库
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%';
3、修改mysql默认配置
mysql配置文件的默认路径在 /etc/mysql/my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
可以看到这个配置文件引入了别的文件,查看配置后,发现需要改的在 /etc/mysql/mysql.conf.d/mysqld.cnf
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
需要修改的地方:
bind-address=127.0.0.1 #根据链接需求进行更改,如果不限制连接用户地址可以注释掉,或者改成0.0.0.0
server-id=1 # mysql服务的唯一ID,主从之间全局唯一
log_bin=mysql-bin # binlog日志
binlog_do_db=dbname # 需要同步的数据库名
重启mysql数据库
sudo systemctl restart mysql.service
4、设置主从
登录数据库,查看File和Position的值
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置从库
mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> change master to
master_host='192.168.11.233', # 主库ip
master_user='slave', # 主库用户
master_password='123456', # 主库密码
master_log_file='binlog.000001', # 上述得到的File
master_log_pos=157; # 上述得到的Position值
Query OK, 0 rows affected, 8 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
查看是否配置成功
show slave status \G;
查看是否全为Yes,No和Connecting均配置不成功
查看主库账户是否有连接权限
show grants for slave;
查看复制账户是否拥有REPLICATION CLIENT、REPLICATION SLAVE、SUPER、RELOAD权限
如果没有的话使用命令授权
grant REPLICATION CLIENT ON *.* TO slave;
grant REPLICATION SLAVE ON *.* TO slave;
grant SUPER ON *.* TO slave;
grant reload on *.* to slave;
FLUSH PRIVILEGES;
配置完成
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
查看状态
show slave status \G;
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
测试是否配置成功
在主机上创建一个数据库
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
在从库上查询数据库,发现test数据库已经同步成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
使用从库创建一张表
mysql> use test;
Database changed
mysql> create table user (
-> name varchar(10),
-> age int);
Query OK, 0 rows affected (0.03 sec)
查看主库是否同步过去
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)