mysql主从搭建

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;

主库设置

1. 配置/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
   

2. 更改完后重启数据库

   systemctl restart mysql.service
   

3. 创建同步账号

   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。

4. 查看配置是否生效

   

5. 创建数据快照

   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端口号。

从数据库配置

1. 配置/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
   

2. 同步数据

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

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

3. 配置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;
   

   上述参数在主库的mysql客户端上运行show master status可看到。

4. 进行测试

   在主库的test数据库里添加数据，在从库上看到是否同步。

参考

• mysql8.0允许外部访问
• ubuntu防火墙关闭与开启
• mysql主从搭建
• Ubuntu18.04搭建MySQL8.0主从复制