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

吾爱主题阅读：150 2022-11-23 14:13:00 评论：0

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;