时间:2022-10-02来源:www.pcxitongcheng.com作者:电脑系统城
环境: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; |
SHELL 复制 全屏
上述参数在主库的mysql客户端上运行
show master status
可看到。
进行测试
在主库的test
数据库里添加数据,在从库上看到是否同步。
2023-03-19
Centos 7.9安装MySQL8.0.32的详细教程2023-03-19
一步步教你利用Mysql存储过程造百万级数据2023-03-19
利用Mysql定时+存储过程创建临时表统计数据的过程问题描述:MYSQL version 5.6.8command 表结构 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE command ( ID INT NOT NULL, NAME VARCHAR(16), D...
2023-03-19
一、Galera Cluster 二、基础环境搭建 三、加入配置参数启动集群 四、 测试 五、ProxySql...
2023-03-17