mysql主从搭建
环境：ubuntu20.04.1，mysql：8.0.22。
主：192.168.87.3
备：192.168.87.6
安装数据库
sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
数据库配置
设置数据库密码
首次安装后，使用 sudo mysql -uroot -p 直接进入，更改root密码操作如下：
use mysql；
ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘root’;
FLUSH PRIVILEGES;
主库设置
配置 /etc/mysql/mysql.conf.d/mysqld.cnf 如下：
[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
更改完后重启数据库
systemctl restart mysql.service
创建同步账号
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。
查看配置是否生效
创建数据快照
mysqldump –all-databases –master-data > dbdump.db
–master-data 这个选项会自动加上CHANGE_MASTER_TO给从机来开始复制过程。在备份时使用 –databases (备份特定的数据库)和 –ignore-tables (排除备份特定的表) 选项，各个数据库和表名之间用空格隔开。
设置远程访问
use mysql;
update user set host=’%’ where user = ‘root’;
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION;
如果此时仍无法访问，查看防火墙是否关闭。关闭命令：
sudo ufw disable
或者开放3306端口号。
从数据库配置
配置 /etc/mysql/mysql.conf.d/mysqld.cnf 如下：
[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并执行如下命令：
set sql_log_bin=0;
source /home/shitianming/Documents/dbdump.db
配置slave
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;
SHELL 复制 全屏
上述参数在主库的mysql客户端上运行 show master status 可看到。
进行测试
在主库的 test 数据库里添加数据，在从库上看到是否同步。
查看更多关于mysql8.0主从复制搭建与配置方案的详细内容...