搭建了一下mysql master slave的环境
在此做一下简单记录
mysql数据库版本:5.7-18
master与slave均采用了如下方式初始化mysql数据库
mkdir -p /data/mysql
useradd mysql
chown -R mysql:mysql /data/
chown -R mysql:mysql /usr/local/mysql*
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/ --user=mysql --initialize-insecure
Master的my.cnf配置:
点击(此处)折叠或打开
- [client]
- port = 3306
- socket = /tmp/mysql.sock
- #default-character-set=utf8
- [mysql]
- #default-character-set=utf8
- [mysqld]
- port = 3306
- socket = /tmp/mysql.sock
- basedir = /usr/local/mysql
- datadir = /data/mysql
- server_id=151
- open_files_limit = 3072
- back_log = 103
- max_connections = 512
- max_connect_errors = 100000
- table_open_cache = 512
- external-locking = FALSE
- max_allowed_packet = 128M
- sort_buffer_size = 2M
- join_buffer_size = 2M
- thread_cache_size = 51
- query_cache_size = 32M
- tmp_table_size = 96M
- max_heap_table_size = 96M
- slow_query_log = 1
- slow_query_log_file = /data/mysql/slow.log
- log-error = /data/mysql/error.log
- long_query_time = 0.05
- log-bin = /data/mysql/mysql-bin
- sync_binlog = 1
- binlog_cache_size = 4M
- max_binlog_cache_size = 128M
- max_binlog_size = 1024M
- expire_logs_days = 7
- key_buffer_size = 32M
- read_buffer_size = 1M
- read_rnd_buffer_size = 16M
- bulk_insert_buffer_size = 64M
- character-set-server=utf8
- default-storage-engine=InnoDB
- binlog_format=row
- #gtid_mode=on
- #log_slave_updates=1
- #enforce_gtid_consistency=1
- interactive_timeout=100
- wait_timeout=100
- transaction_isolation = REPEATABLE-READ
- #innodb_additional_mem_pool_size = 16M
- innodb_buffer_pool_size = 1434M
- innodb_data_file_path = ibdata1:1024M:autoextend
- innodb_flush_log_at_trx_commit = 1
- innodb_log_buffer_size = 16M
- innodb_log_file_size = 256M
- innodb_log_files_in_group = 2
- innodb_max_dirty_pages_pct = 50
- innodb_file_per_table = 1
- innodb_locks_unsafe_for_binlog = 0
- [mysqldump]
- quick
- max_allowed_packet = 32M
Slave的配置文件:/etc/my.cnf
点击(此处)折叠或打开
- [client]
- port = 3306
- socket = /tmp/mysql.sock
- #default-character-set=utf8
- [mysql]
- #default-character-set=utf8
- [mysqld]
- port = 3306
- socket = /tmp/mysql.sock
- basedir = /usr/local/mysql
- datadir = /data/mysql
- server_id=152
- #master slave replicat
- #master-host=192.168.43.151
- #master-user=repl
- #master-password=repl
- relay-log=/data/mysql/mysql-replay-bin
- master-info-file = /data/mysql/mysql-master.info
- relay-log-info-file = /data/mysql/mysql-relay-log.info
- open_files_limit = 3072
- back_log = 103
- max_connections = 512
- max_connect_errors = 100000
- table_open_cache = 512
- external-locking = FALSE
- max_allowed_packet = 128M
- sort_buffer_size = 2M
- join_buffer_size = 2M
- thread_cache_size = 51
- query_cache_size = 32M
- tmp_table_size = 96M
- max_heap_table_size = 96M
- slow_query_log = 1
- slow_query_log_file = /data/mysql/slow.log
- log-error = /data/mysql/error.log
- long_query_time = 0.05
- log-bin = /data/mysql/mysql-bin
- sync_binlog = 1
- binlog_cache_size = 4M
- max_binlog_cache_size = 128M
- max_binlog_size = 1024M
- expire_logs_days = 7
- key_buffer_size = 32M
- read_buffer_size = 1M
- read_rnd_buffer_size = 16M
- bulk_insert_buffer_size = 64M
- character-set-server=utf8
- default-storage-engine=InnoDB
- binlog_format=row
- #gtid_mode=on
- #log_slave_updates=1
- #enforce_gtid_consistency=1
- interactive_timeout=100
- wait_timeout=100
- transaction_isolation = REPEATABLE-READ
- #innodb_additional_mem_pool_size = 16M
- innodb_buffer_pool_size = 1434M
- innodb_data_file_path = ibdata1:1024M:autoextend
- innodb_flush_log_at_trx_commit = 1
- innodb_log_buffer_size = 16M
- innodb_log_file_size = 256M
- innodb_log_files_in_group = 2
- innodb_max_dirty_pages_pct = 50
- innodb_file_per_table = 1
- innodb_locks_unsafe_for_binlog = 0
- [mysqldump]
- quick
- max_allowed_packet = 32M
参考:
https://blog.csdn.net/edwzhang/article/details/8819629
初始话完成之后,在master通过mysqldump导出mysql数据库
会话1:
[root@mysql01 ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 581 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
这个Master Status很重要,在其后的slave配置中需要依赖它
mysqldump -u root -p --all-databases --master-data > /root/dbdump.db
导出之后,释放锁
mysql> unlock tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 5
Current database: *** NONE ***
Query OK, 0 rows affected (0.00 sec)
slave端导入, 导入之后重启mysql服务
mysql -u root -p < /root/dbdump.db
在slave端启用复制:
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.43.151',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl',
-> MASTER_LOG_FILE='mysql-bin.000017',
-> MASTER_LOG_POS=581;
Query OK, 0 rows affected, 2 warnings (0.30 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在master端进行测试:
mysql> create database test2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Query OK, 1 row affected (0.28 sec)
mysql>
mysql> show slave staus
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'staus' at line 1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 743 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> use test02
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***
ERROR 1049 (42000): Unknown database 'test02'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test2 |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test2
Database changed
mysql>
mysql>
mysql> create table mytest01(pid int, nme varchar(100));
Query OK, 0 rows affected (0.34 sec)
mysql>
mysql> insert into mytest01 values(1, 'AAAA');
Query OK, 1 row affected (0.04 sec)
mysql>
如果配置正确,应当可以在slave端看到数据
参考:https://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/
https://blog.wu-boy.com/2008/12/mysql-%E5%AF%A6%E5%81%9A-mysql-master-master-replication-%E5%90%8C%E6%AD%A5/
https://www.tecmint.com/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-2156333/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-2156333/