Mysql中Master-slave如何配置
发布时间：2022-01-11 13:51:23 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下Mysql中Master-slave如何配置，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 搭建了一下mysql master slave的环境 在此做一下简单记录 mysql数据库版本
|
小编给大家分享一下Mysql中Master-slave如何配置，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 搭建了一下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 注意：master-host这个参数5.7已经不支持。 参考： 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端看到数据 以上是“Mysql中Master-slave如何配置”这篇文章的所有内容，感谢各位的阅读！ （编辑：91站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
