Mysql Master-slave複製簡單配置記錄

abstractcyj發表於2018-06-19
MySqlAST

搭建了一下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配置：

點選(此處)摺疊或開啟

  1. [client]
  2. port = 3306
  3. socket = /tmp/mysql.sock
  4. #default-character-set=utf8
  5. [mysql]
  6. #default-character-set=utf8
  7. [mysqld]
  8. port = 3306
  9. socket = /tmp/mysql.sock
  10. basedir = /usr/local/mysql
  11. datadir = /data/mysql
  12. server_id=151
  13. open_files_limit = 3072
  14. back_log = 103
  15. max_connections = 512
  16. max_connect_errors = 100000
  17. table_open_cache = 512
  18. external-locking = FALSE
  19. max_allowed_packet = 128M
  20. sort_buffer_size = 2M
  21. join_buffer_size = 2M
  22. thread_cache_size = 51
  23. query_cache_size = 32M
  24. tmp_table_size = 96M
  25. max_heap_table_size = 96M
  26. slow_query_log = 1
  27. slow_query_log_file = /data/mysql/slow.log
  28. log-error = /data/mysql/error.log
  29. long_query_time = 0.05
  30. log-bin = /data/mysql/mysql-bin
  31. sync_binlog = 1
  32. binlog_cache_size = 4M
  33. max_binlog_cache_size = 128M
  34. max_binlog_size = 1024M
  35. expire_logs_days = 7
  36. key_buffer_size = 32M
  37. read_buffer_size = 1M
  38. read_rnd_buffer_size = 16M
  39. bulk_insert_buffer_size = 64M
  40. character-set-server=utf8
  41. default-storage-engine=InnoDB
  42. binlog_format=row
  43. #gtid_mode=on
  44. #log_slave_updates=1
  45. #enforce_gtid_consistency=1
  46. interactive_timeout=100
  47. wait_timeout=100
  48. transaction_isolation = REPEATABLE-READ
  49. #innodb_additional_mem_pool_size = 16M
  50. innodb_buffer_pool_size = 1434M
  51. innodb_data_file_path = ibdata1:1024M:autoextend
  52. innodb_flush_log_at_trx_commit = 1
  53. innodb_log_buffer_size = 16M
  54. innodb_log_file_size = 256M
  55. innodb_log_files_in_group = 2
  56. innodb_max_dirty_pages_pct = 50
  57. innodb_file_per_table = 1
  58. innodb_locks_unsafe_for_binlog = 0
  59. [mysqldump]
  60. quick
  61. max_allowed_packet = 32M

Slave的配置檔案：/etc/my.cnf

點選(此處)摺疊或開啟

  1. [client]
  2. port = 3306
  3. socket = /tmp/mysql.sock
  4. #default-character-set=utf8
  5. [mysql]
  6. #default-character-set=utf8
  7. [mysqld]
  8. port = 3306
  9. socket = /tmp/mysql.sock
  10. basedir = /usr/local/mysql
  11. datadir = /data/mysql
  12. server_id=152
  13. #master slave replicat
  14. #master-host=192.168.43.151
  15. #master-user=repl
  16. #master-password=repl
  17. relay-log=/data/mysql/mysql-replay-bin
  18. master-info-file = /data/mysql/mysql-master.info
  19. relay-log-info-file = /data/mysql/mysql-relay-log.info
  20. open_files_limit = 3072
  21. back_log = 103
  22. max_connections = 512
  23. max_connect_errors = 100000
  24. table_open_cache = 512
  25. external-locking = FALSE
  26. max_allowed_packet = 128M
  27. sort_buffer_size = 2M
  28. join_buffer_size = 2M
  29. thread_cache_size = 51
  30. query_cache_size = 32M
  31. tmp_table_size = 96M
  32. max_heap_table_size = 96M
  33. slow_query_log = 1
  34. slow_query_log_file = /data/mysql/slow.log
  35. log-error = /data/mysql/error.log
  36. long_query_time = 0.05
  37. log-bin = /data/mysql/mysql-bin
  38. sync_binlog = 1
  39. binlog_cache_size = 4M
  40. max_binlog_cache_size = 128M
  41. max_binlog_size = 1024M
  42. expire_logs_days = 7
  43. key_buffer_size = 32M
  44. read_buffer_size = 1M
  45. read_rnd_buffer_size = 16M
  46. bulk_insert_buffer_size = 64M
  47. character-set-server=utf8
  48. default-storage-engine=InnoDB
  49. binlog_format=row
  50. #gtid_mode=on
  51. #log_slave_updates=1
  52. #enforce_gtid_consistency=1
  53. interactive_timeout=100
  54. wait_timeout=100
  55. transaction_isolation = REPEATABLE-READ
  56. #innodb_additional_mem_pool_size = 16M
  57. innodb_buffer_pool_size = 1434M
  58. innodb_data_file_path = ibdata1:1024M:autoextend
  59. innodb_flush_log_at_trx_commit = 1
  60. innodb_log_buffer_size = 16M
  61. innodb_log_file_size = 256M
  62. innodb_log_files_in_group = 2
  63. innodb_max_dirty_pages_pct = 50
  64. innodb_file_per_table = 1
  65. innodb_locks_unsafe_for_binlog = 0
  66. [mysqldump]
  67. quick
  68. 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端看到資料

參考：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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章