当前位置：首页 > 数据库 > Mysql > 详细页面

mysql8.0主从复制搭建与配置方案

时间：2022-10-02来源：www.pcxitongcheng.com作者：电脑系统城

mysql主从搭建

环境：ubuntu20.04.1，mysql：8.0.22。
主：192.168.87.3
备：192.168.87.6

安装数据库

1
2
3 sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev

数据库配置

设置数据库密码

首次安装后，使用sudo mysql -uroot -p直接进入，更改root密码操作如下：

1
2
3 use mysql；
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
FLUSH PRIVILEGES;

主库设置

配置/etc/mysql/mysql.conf.d/mysqld.cnf如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 [mysqld]

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
datadir = /var/lib/mysql

bind-address = 192.168.87.3 # 本机ip
mysqlx-bind-address = 127.0.0.1

key_buffer_size = 16M

myisam-recover-options = BACKUP

max_connections = 1000

log_error = /var/log/mysql/error.log

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog_do_db = test
binlog_ignore_db = mysql
binlog_format = row
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

更改完后重启数据库

1	`systemctl restart mysql.service`

创建同步账号

1 2	`CREATE` `USER` `'sync'@'192.168.87.6'` `IDENTIFIED` `WITH` `mysql_native_password` `BY` `'sync';` `grant` `replication slave` `on` `.` `to` `'sync'@'192.168.87.6';`

192.168.87.6为从数据库的IP。

查看配置是否生效

创建数据快照

1	`mysqldump --all-databases --master-data > dbdump.db`

–master-data这个选项会自动加上CHANGE_MASTER_TO给从机来开始复制过程。在备份时使用–databases(备份特定的数据库)和–ignore-tables(排除备份特定的表) 选项，各个数据库和表名之间用空格隔开。

设置远程访问

1
2
3
4 use mysql;
update user set host='%' where user = 'root';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

如果此时仍无法访问，查看防火墙是否关闭。关闭命令：

1	`sudo ufw disable`

或者开放3306端口号。

从数据库配置

配置/etc/mysql/mysql.conf.d/mysqld.cnf如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 [mysqld]

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
datadir = /var/lib/mysql

bind-address = 192.168.87.6
mysqlx-bind-address = 127.0.0.1
key_buffer_size = 16M

myisam-recover-options = BACKUP

log_error = /var/log/mysql/error.log

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds = 2592000
max_binlog_size = 100M
binlog_do_db = test
binlog_ignore_db = mysql

同步数据

在主库上dump的文件scp到从库上，然后登录mysql并执行如下命令：

1 2	`set sql_log_bin=0;` `source /home/shitianming/Documents/dbdump.db`

配置slave

1
2
3
4
5
6
7 CHANGE MASTER TO
MASTER_HOST='192.168.87.3',
MASTER_USER='sync',
MASTER_PASSWORD='sync',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=730;