昨天晚上 MySQL 机器的 CPU 有点高, 感觉有点扛不住了. 新项目只是一个尝试性的项目, 不确定是否有市场, 所有并没有搭建主从
启用主库 binlog
1
2
3
4
5
6
7
8
9
10
| [mysqld]
bind-address=0.0.0.0
server-id=1
skip-networking=0
skip-name-resolve=1
symbolic-links=0
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-do-table=mysql.%
expire_logs_days=3
|
重启主库服务 后查看 binlog 位置
1
2
3
4
5
6
| MariaDB [(none)]> show master status\G;
***************** 1. row ****************
File: mysql-bin.000033 #当前记录的日志
Position: 328 #日志中记录的位置
Binlog_Do_DB:
Binlog_Ignore_DB:
|
添加主从复制的用户
1
2
| MariaDB [(none)]> grant replication slave on *.* to 'account'@'%' identified by '123456';
MariaDB [(none)]> flush privileges;
|
启用从库同步
1
2
3
4
5
6
7
| server-id=2
log-bin=mysql-bin
log-slave-updates
sync_binlog=0
innodb_flush_log_at_trx_commit=0
slave-net-timeout=60
log_bin_trust_function_creators=1
|
安装 xtrabackup 工具导出数据库数据
1
2
3
| $ wget https://www.percona.com/redir/downloads/percona-release/ubuntu/latest/percona-release_0.1-4.xenial_all.deb
$ sudo dpkg -i percona-release_0.1-4.xenial_all.deb
$ sudo apt update && sudo apt-get install percona-xtrabackup-24 -y
|
创建备份和导出数据库
1
2
| $ innobackupex --defaults-file=/data/meiyuan/mysql/my.cnf --host=127.0.0.1 --port=3306 --databases="meiyuan" --user=root --password=123456 /data/meiyuan/mysql/backup_full
$ mysqldump -h127.0.0.1 -uroot -P123456 -B -R --single-transaction --master-data=2> dump.sql
|
建立主从并查看状态
1
2
3
4
5
6
7
8
9
| MariaDB [(none)]> change master to master_host='192.168.56.107',master_user='account',master_password='123456',master_log_file='mysql-bin.000033',master_log_pos=328;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show master status;
+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
| mysql-bin.000001 | 328 | | |
+
1 row in set (0.002 sec)
|