mysql主从同步

主数据库

1.添加用户，

create user 'replication'@'192.168.0.10' identified by 'password';

replication只是个名字，这样一下子能看出来，可以设置成别的
password需要设置成想要设置的密码

有的mysql配置成只能本地访问，如果没有新的指定ip的用户，可能远程连接不了，也就不能传递数据了

2.配置my.cnf
#vi /etc/my.cnf
在[mysqld]的下面加入下面代码：

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=test_data_base
binlog_ignore_db=mysql

server-id=1中的1可以任定义，只要是唯一的就行。
binlog-do-db=test_data_base是表示只备份test_data_base这一个数据库，多个数据库的话，可以写多行。
binlog_ignore_db=mysql表示忽略备份mysql。
不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库。

注意不是[mysqld_safe]，有的my.cnf文件有很多注释，[mysqld]太不显眼了，容易忽视

 

3.重启MySQL:
#service mysqld restart

4.登录MySQL服务器。
#mysql -uroot -p

5.再次执行

grant replication slave on *.* to 'replication'@'192.168.0.10' identified by 'test123';

第一步，可以放到第五步前面执行

 

从数据库

（docker中运行的mysql，docker所在的主机已经有mysql在运行，没有更多主机了）

1.配置从数据库

#vi /etc/my.cnf
在[mysqld]的下面加入下面代码：
server-id=2

2.重启mysql

如果不在docker中，要运行：service mysqld restart

 

3.配置从数据库所要同步的主数据库连接

mysql>CHANGE MASTER TO
MASTER_HOST='X.X.X.X',
MASTER_USER='replication',
MASTER_PASSWORD='test123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120,
MASTER_CONNECT_RETRY=10;

 

4.启动从数据库同步

mysql>start slave

不报错最好，

报错，还说server_id一样，明明设置的不一样

mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.04 sec)

居然是1，明明设置的是2
只能再用命令设置一下

mysql> set global server_id=2;

再次start slave看到
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 

如果主数据库中有数据。

docker中从数据库创建同名数据库
 

mysql>create database test_data_base;

主数据库备份当前数据
 

mysqldump -uroot -ptest123 test_data_base > backup.sql

主数据库数据导入从数据库

cat ./backup.sql |  docker exec -i 18ad /usr/bin/mysql -uroot -ptest123 test_data_base

如果主数据库开启主从前就已经数据，用以上方式导入了，可实现数据同步，同理可用于数据迁移，mysqldump后建立主从，然后再恢复mysqldump导出的数据。

docker中的从数据库能看到表和数据都出来了，18ad是容器的名字

如果主数据库再docker中，

docker exec 18ad /usr/bin/mysqldump -uroot -ptest123 test_dsata_base > backup.sql

备份到docker所在主机的backup.sql文件中

 

小贴士：

主数据库的my.cnf配置

binlog-do-db=test_data_base

后面不要加逗号分号那些鬼东西，网络要确保是通的，

 

备份数据库时最好上锁，不能发生写操作，不然数据又不一致了，log-bin只能同步开始记录后的操作。。

 

根据log-bin查看最近执行的sql操作

binlog位置通过/etc/my.cnf查看，log-bin=.........

mysqlbinlog /var/lib/mysql/mysql-bin.000011 | grep -E "table_user|table_login"

获取指定时间以后的sql语句
mysqlbinlog /var/lib/mysql/mysql-bin.000011 --start-datetime="2019-05-22 10:53:09"  | grep -E "table_user|table_login"

获取时间区间内的sql语句
mysqlbinlog /var/lib/mysql/mysql-bin.000011 --start-datetime="2019-05-22 10:53:09"  --stop-datetime="2019-05-22 16:53:09" | grep -E "table_user|table_login"


指定数据库
 --database=geeRunner
以免多个数据库同时操作，过滤表的话，可以使用grep

 

mysql int类型当字符串，并且拼接

update table_test set name =concat(cast(user_id as char), 'test');
cast类型转换
concat字符串拼接

update table_test set name =concat(user_id,'dddcc');
也可以直接拼接

批量执行文件中的sql语句，以及导出结构和数据

mysql -uroot -p tb_test < ./update_test_user_name_sql.txt

//mysqldump导出的sql语句也可以批量执行导入，
mysql -uroot -p tb_test < ./backup.sql

导出整个数据库结构和数据
mysqldump -h localhost -uroot -p123456 database > dump.sql 

导出单个数据表结构和数据
mysqldump -h localhost -uroot -p123456  database table > dump.sql
 

导出整个数据库结构（不包含数据）
mysqldump -h localhost -uroot -p123456  -d database > dump.sql 

导出单个数据表结构（不包含数据）
mysqldump -h localhost -uroot -p123456  -d database table > dump.sql

命令行中可以不输入密码，敲回车后会提示输入密码，那个时候输错了就只是没有数据

所要执行的insert、update语句放入update_test_user_name_sql.txt中即可。tb_test是数据库的名称，不是密码