9.2 主从部署

1cat > /mysqljem/master/my.cnf <<"EOF"
2[mysqld]
3user=mysql
4port=3306
5character_set_server=utf8mb4
6secure_file_priv=''
7server-id = 802733261
8log-bin =
9binlog_format=row
10binlog-ignore-db = mysql
11binlog-ignore-db = information_schema
12binlog-ignore-db = performance_schema
13binlog-ignore-db = sys
14replicate_ignore_db=information_schema
15replicate_ignore_db=performance_schema
16replicate_ignore_db=mysql
17replicate_ignore_db=sys
18skip-name-resolve
19report_host=172.72.0.72
20EOF
Copy

1cat > /mysqljem/slave/my.cnf <<"EOF"
2[mysqld]
3user=mysql
4port=3306
5character_set_server=utf8mb4
6secure_file_priv=''
7server-id = 802733262
8log-bin =
9binlog_format=row
10binlog-ignore-db = mysql
11binlog-ignore-db = information_schema
12binlog-ignore-db = performance_schema
13binlog-ignore-db = sys
14replicate_ignore_db=information_schema
15replicate_ignore_db=performance_schema
16replicate_ignore_db=mysql
17replicate_ignore_db=sys
18skip_name_resolve
19report_host=172.72.0.73
20EOF
21
22说明：
23binlog-ignore-db：此参数表示不记录指定的数据库的二进制日志
24replicate_ignore_db：用来设置不需要同步的库
2510.5.3主库创建用户
26mysql> create user repl@'172.72.0.73' identified with mysql_native_password by 'root';
27mysql> grant all on *.* to repl@'172.72.0.73' with grant option;
28mysql> flush privileges;
29注意：172.72.0.73为从库ip
Copy

1【主库dump导出jemdb数据库】
2mysqldump -uroot -proot -h172.72.0.72 -P3306 \
3--single-transaction \
4--hex-blob --routines --events --triggers --source-data=2 \
5--set-gtid-purged=OFF --databases jemdb --default-character-set=utf8 \
6--max_allowed_packet=512M > /tmp/salve.sql
7注意：如果是所有数据库，就是-A
8以下在主库操作插入数据
9mysql> show master status \G;
10mysql> insert into jemdb.mytb1 values(3,'a'),(4,'b');
11mysql> show slave hosts;
12mysql> select @@hostname,@@server_id,@@server_uuid;
13 
14【从库同步主库数据】
15① 通过以下方法查找同步的启示位置
16[root@jeames ~]# grep "CHANGE MASTER" /tmp/salve.sql
17-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=1144;
18② dump导入库
19mysql -uroot -proot -h172.72.0.72 -P3306 < /tmp/salve.sql
20③ 登陆从库同步
21[root@jeames ~]# mysql -uroot -proot -h172.72.0.73 -P3306
22mysql> change master to
23master_host='172.72.0.72',
24master_user='repl',
25master_password='root',
26master_port=3306,
27master_log_file='master-bin.000002',
28master_log_pos=1144;
29注意：master-bin.000002为主库binlog，1144为binlog中的MASTER_LOG_POS
30④ 从库同步开始
31mysql> start slave;
32【主库确认】
33mysql> show slave hosts;
Copy