单实例搭建

规划

卸载自带数据库

  1. rpm -qa | grep mysql
  2. rpm -qa | grep mariadb
  3. rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

创建用户

  1. groupadd mysql
  2. useradd -g mysql mysql -s /sbin/nologin

解压并创建配置文件及目录

  1. tar xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz -C /usr/local
  2. mv mysql-8.0.19-linux-glibc2.12-x86_64/ mysql

配置文件/etc/my.cnf

  1. [client]
  2. port    = 3406
  3. socket    = /tmp/mysql.sock
  5. [mysql]
  6. prompt="\u@db \R:\m:\s [\d]> "
  7. no-auto-rehash
  9. [mysqld]
  10. server-id = 3306100
  11. user    = mysql
  12. port    = 3406
  13. basedir    = /usr/local/mysql
  14. datadir    = /data/mysql/
  15. socket    = /tmp/mysql.sock
  16. pid-file = db.pid
  18. character-set-server = utf8mb4
  20. skip_name_resolve = 1
  21. open_files_limit    = 65535
  22. back_log = 1024
  23. max_connections = 512
  24. max_connect_errors = 1000000
  25. table_open_cache = 1024
  26. table_definition_cache = 1024
  27. table_open_cache_instances = 64
  28. thread_stack = 512K
  29. external-locking = FALSE
  30. max_allowed_packet = 32M
  31. sort_buffer_size = 4M
  32. join_buffer_size = 4M
  33. thread_cache_size = 768
  35. interactive_timeout = 600
  36. wait_timeout = 600
  37. tmp_table_size = 32M
  38. max_heap_table_size = 32M
  39. slow_query_log = 1
  40. slow_query_log_file = /data/mysql/slow.log
  41. log-error = /data/mysql/error.log
  42. long_query_time = 0.1
  44. log-bin = /data/mysql/mysql-binlog
  45. sync_binlog = 1
  46. binlog_cache_size = 4M
  47. max_binlog_cache_size = 1G
  48. max_binlog_size = 1G
  49. gtid_mode = on
  50. enforce_gtid_consistency = 1
  51. log_slave_updates
  52. binlog_format = row
  53. relay_log_recovery = 1
  54. relay-log-purge = 1
  55. key_buffer_size = 32M
  56. read_buffer_size = 8M
  57. read_rnd_buffer_size = 4M
  58. bulk_insert_buffer_size = 64M
  59. #myisam_sort_buffer_size = 128M
  60. #myisam_max_sort_file_size = 10G
  61. #myisam_repair_threads = 1
  62. lock_wait_timeout = 3600
  63. explicit_defaults_for_timestamp = 1
  64. innodb_thread_concurrency = 0
  65. innodb_sync_spin_loops = 100
  66. innodb_spin_wait_delay = 30
  67. master_info_repository = TABLE
  68. relay_log_info_repository = TABLE
  69. slave_parallel_type=LOGICAL_CLOCK
  71. transaction_isolation = REPEATABLE-READ
  72. #innodb_additional_mem_pool_size = 16M
  73. innodb_buffer_pool_size = 1024M
  74. innodb_buffer_pool_instances = 8
  75. innodb_buffer_pool_load_at_startup = 1
  76. innodb_buffer_pool_dump_at_shutdown = 1
  77. innodb_data_file_path = ibdata1:1G:autoextend
  78. innodb_flush_log_at_trx_commit = 1
  79. innodb_log_buffer_size = 32M
  80. innodb_log_file_size = 2G
  81. innodb_log_files_in_group = 2
  82. #innodb_max_undo_log_size = 1G
  84. # 根据您的服务器IOPS能力适当调整
  85. # 一般配普通SSD盘的话，可以调整到 10000 - 20000
  86. # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
  87. innodb_io_capacity = 4000
  88. innodb_io_capacity_max = 8000
  89. innodb_flush_neighbors = 0
  90. innodb_write_io_threads = 8
  91. innodb_read_io_threads = 8
  92. innodb_purge_threads = 4
  93. innodb_page_cleaners = 4
  94. innodb_open_files = 65535
  95. innodb_max_dirty_pages_pct = 50
  96. innodb_flush_method = O_DIRECT
  97. innodb_lru_scan_depth = 4000
  98. innodb_checksum_algorithm = crc32
  99. #innodb_file_format = Barracuda
  100. #innodb_file_format_max = Barracuda
  101. innodb_lock_wait_timeout = 10
  102. innodb_rollback_on_timeout = 1
  103. innodb_print_all_deadlocks = 1
  104. innodb_file_per_table = 1
  105. innodb_online_alter_log_max_size = 4G
  106. innodb_stats_on_metadata = 0
  108. innodb_status_file = 1
  109. # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
  110. innodb_status_output = 0
  111. innodb_status_output_locks = 0
  113. #performance_schema
  114. performance_schema = 1
  115. performance_schema_instrument = '%=on'
  117. #innodb monitor
  118. innodb_monitor_enable="module_innodb"
  119. innodb_monitor_enable="module_server"
  120. innodb_monitor_enable="module_dml"
  121. innodb_monitor_enable="module_ddl"
  122. innodb_monitor_enable="module_trx"
  123. innodb_monitor_enable="module_os"
  124. innodb_monitor_enable="module_purge"
  125. innodb_monitor_enable="module_log"
  126. innodb_monitor_enable="module_lock"
  127. innodb_monitor_enable="module_buffer"
  128. innodb_monitor_enable="module_index"
  129. innodb_monitor_enable="module_ibuf_system"
  130. innodb_monitor_enable="module_buffer_page"
  131. innodb_monitor_enable="module_adaptive_hash"
  133.  # Group Replication
  134. #server_id = 1003306
  135. #gtid_mode = ON
  136. #enforce_gtid_consistency = ON
  137. #master_info_repository = TABLE
  138. #relay_log_info_repository = TABLE
  139. binlog_checksum = NONE
  140. #log_slave_updates = ON
  141. #log_bin = binlog
  142. #binlog_format= ROW
  143. transaction_write_set_extraction = XXHASH64
  145. # 未来可能被弃用的变量，会出现告警信息，binlog_expire_logs_seconds用来代替
  146. # expire_logs_days = 7
  147. binlog_expire_logs_seconds = 7
  149. # 8版本弃用的变量
  150. # loose-group_replication_group_name = 'e842862c-9b12-11e8-8131-080027f1fd08'
  151. # internal_tmp_disk_storage_engine = InnoDB # 8版本不再支持，默认引擎
  152. # query_cache_size = 0 # 8版本不再支持这两个参数
  153. # query_cache_type = 0
  154. # loose-group_replication_start_on_boot = off
  155. # loose-group_replication_local_address = 'enmoedu:33066'
  156. # loose-group_replication_group_seeds ='enmoedu1:33067,enmoedu2:33068,enmoedu:33066'
  157. # loose-group_replication_bootstrap_group = off
  158. # loose-group_replication_single_primary_mode=off
  159. # loose-group_replication_enforce_update_everywhere_checks=true
  161. [mysqldump]
  162. quick
  163. max_allowed_packet = 32M

创建目录

  1. # 创建数据目录和配置文件目录
  2. mkdir -p /data/mysql/
  3. chown mysql:mysql -R /data/mysql
  4. chown mysql:mysql -R /usr/local/mysql/

初始化服务

  1. cd /usr/local/mysql/bin
  3. ./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql

查看密码

  1. more  /data/mysql/error.log 
  2. 2021-06-07T12:28:36.732739Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_log
  3. s_seconds instead.
  4. 2021-06-07T12:28:36.732982Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.19) initializing of server in progress as process 20310
  5.  100 200 300 400 500 600 700 800 900 1000
  6.  100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
  7.  100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
  8. 2021-06-07T12:28:49.385707Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: >ad,tMNX#7,p

添加自动启动

  1. # 使用systemctl来管理mysql
  2. cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
  3. chkconfig --add mysqld
  4. chkconfig mysqld on

启动数据库并修改密码

如果添加到了systemd服务，也可以使用systectl进行管理启动

  1. # 安全启动
  2. ./mysqld_safe --defaults-file=/etc/my.cnf &
  3. # 配置mysql环境变量
  4. echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
  5. source /etc/profile
  7. mysql -uroot -p
  8. alter user 'root'@'localhost' identified by 'root@123';
  9. flush privileges;
  11. # 关闭数据库
  12. ./mysqladmin shutdown -uroot -p'password'

多实例

一台主机多个mysql实例

单机多实例有两种创建方式：

一、本办法；直接解压两份文件进行配置，如下实例1和实例2

二、部署一个服务后多次初始化，简要操作如下：

  1. 创建datadir数据目录，并赋权（所属用户和组）
  2. 使用原来的服务进行多次初始化，初始化时指定新创建的目录
  3. 复制my.cnf配置文件，修改端口和sock和目录位置
  4. 启动服务并修改密码

实例1

解压文件&创建配置文件

  1. groupadd mysql
  2. useradd -g mysql mysql -s /sbin/nologin
  4. tar xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz -C /usr/local
  5. cp -r mysql-8.0.19-linux-glibc2.12-x86_64/ mysql8-1
  7. mkdir /usr/local/mysql8-1/data
  8. mkdir /usr/local/mysql8-1/etc
  9. chown -R mysql:mysql /usr/local/mysql8-1/

/usr/local/mysql8-1/etc/my.cnf

  1. [client]
  2. port    = 3406
  3. socket  = /usr/local/mysql8-1/mysql.sock
  5. [mysql]
  6. prompt="\u@db \R:\m:\s [\d]> "
  7. no-auto-rehash
  9. [mysqld]
  10. server-id = 3306100
  11. user    = mysql
  12. port    = 3406
  13. basedir = /usr/local/mysql8-1
  14. datadir = /usr/local/mysql8-1/data
  15. socket  = /usr/local/mysql8-1/mysql.sock
  16. pid-file = db.pid
  18. character-set-server = utf8mb4
  20. skip_name_resolve = 1
  21. open_files_limit    = 65535
  22. back_log = 1024
  23. max_connections = 512
  24. max_connect_errors = 1000000
  25. table_open_cache = 1024
  26. table_definition_cache = 1024
  27. table_open_cache_instances = 64
  28. thread_stack = 512K
  29. external-locking = FALSE
  30. max_allowed_packet = 32M
  31. sort_buffer_size = 4M
  32. join_buffer_size = 4M
  33. thread_cache_size = 768
  35. interactive_timeout = 600
  36. wait_timeout = 600
  37. tmp_table_size = 32M
  38. max_heap_table_size = 32M
  39. slow_query_log = 1
  40. slow_query_log_file = /usr/local/mysql8-1/data/slow.log
  41. log-error = /usr/local/mysql8-1/data/error.log
  42. long_query_time = 0.1
  44. log-bin = /usr/local/mysql8-1/data/mysql-binlog
  45. sync_binlog = 1
  46. binlog_cache_size = 4M
  47. max_binlog_cache_size = 1G
  48. max_binlog_size = 1G
  49. gtid_mode = on
  50. enforce_gtid_consistency = 1
  51. log_slave_updates
  52. binlog_format = row
  53. relay_log_recovery = 1
  54. relay-log-purge = 1
  55. key_buffer_size = 32M
  56. read_buffer_size = 8M
  57. read_rnd_buffer_size = 4M
  58. bulk_insert_buffer_size = 64M
  59. #myisam_sort_buffer_size = 128M
  60. #myisam_max_sort_file_size = 10G
  61. #myisam_repair_threads = 1
  62. lock_wait_timeout = 3600
  63. explicit_defaults_for_timestamp = 1
  64. innodb_thread_concurrency = 0
  65. innodb_sync_spin_loops = 100
  66. innodb_spin_wait_delay = 30
  67. master_info_repository = TABLE
  68. relay_log_info_repository = TABLE
  69. slave_parallel_type=LOGICAL_CLOCK
  71. transaction_isolation = REPEATABLE-READ
  72. #innodb_additional_mem_pool_size = 16M
  73. innodb_buffer_pool_size = 1024M
  74. innodb_buffer_pool_instances = 8
  75. innodb_buffer_pool_load_at_startup = 1
  76. innodb_buffer_pool_dump_at_shutdown = 1
  77. innodb_data_file_path = ibdata1:1G:autoextend
  78. innodb_flush_log_at_trx_commit = 1
  79. innodb_log_buffer_size = 32M
  80. innodb_log_file_size = 2G
  81. innodb_log_files_in_group = 2
  82. #innodb_max_undo_log_size = 1G
  84. # 根据您的服务器IOPS能力适当调整
  85. # 一般配普通SSD盘的话，可以调整到 10000 - 20000
  86. # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
  87. innodb_io_capacity = 4000
  88. innodb_io_capacity_max = 8000
  89. innodb_flush_neighbors = 0
  90. innodb_write_io_threads = 8
  91. innodb_read_io_threads = 8
  92. innodb_purge_threads = 4
  93. innodb_page_cleaners = 4
  94. innodb_open_files = 65535
  95. innodb_max_dirty_pages_pct = 50
  96. innodb_flush_method = O_DIRECT
  97. innodb_lru_scan_depth = 4000
  98. innodb_checksum_algorithm = crc32
  99. #innodb_file_format = Barracuda
  100. #innodb_file_format_max = Barracuda
  101. innodb_lock_wait_timeout = 10
  102. innodb_rollback_on_timeout = 1
  103. innodb_print_all_deadlocks = 1
  104. innodb_file_per_table = 1
  105. innodb_online_alter_log_max_size = 4G
  106. innodb_stats_on_metadata = 0
  108. innodb_status_file = 1
  109. # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
  110. innodb_status_output = 0
  111. innodb_status_output_locks = 0
  113. #performance_schema
  114. performance_schema = 1
  115. performance_schema_instrument = '%=on'
  117. #innodb monitor
  118. innodb_monitor_enable="module_innodb"
  119. innodb_monitor_enable="module_server"
  120. innodb_monitor_enable="module_dml"
  121. innodb_monitor_enable="module_ddl"
  122. innodb_monitor_enable="module_trx"
  123. innodb_monitor_enable="module_os"
  124. innodb_monitor_enable="module_purge"
  125. innodb_monitor_enable="module_log"
  126. innodb_monitor_enable="module_lock"
  127. innodb_monitor_enable="module_buffer"
  128. innodb_monitor_enable="module_index"
  129. innodb_monitor_enable="module_ibuf_system"
  130. innodb_monitor_enable="module_buffer_page"
  131. innodb_monitor_enable="module_adaptive_hash"
  133.  # Group Replication
  134. #server_id = 1003306
  135. #gtid_mode = ON
  136. #enforce_gtid_consistency = ON
  137. #master_info_repository = TABLE
  138. #relay_log_info_repository = TABLE
  139. binlog_checksum = NONE
  140. #log_slave_updates = ON
  141. #log_bin = binlog
  142. #binlog_format= ROW
  143. transaction_write_set_extraction = XXHASH64
  145. # expire_logs_days参数未来可能被弃用的变量，会出现告警信息
  146. binlog_expire_logs_seconds = 7
  148. # 8版本弃用的变量
  149. # loose-group_replication_group_name = 'e842862c-9b12-11e8-8131-080027f1fd08'
  150. # internal_tmp_disk_storage_engine = InnoDB # 8版本不再支持，默认引擎
  151. # query_cache_size = 0 # 8版本不再支持这两个参数
  152. # query_cache_type = 0
  153. # loose-group_replication_start_on_boot = off
  154. # loose-group_replication_local_address = 'enmoedu:33066'
  155. # loose-group_replication_group_seeds ='enmoedu1:33067,enmoedu2:33068,enmoedu:33066'
  156. # loose-group_replication_bootstrap_group = off
  157. # loose-group_replication_single_primary_mode=off
  158. # loose-group_replication_enforce_update_everywhere_checks=true
  160. [mysqldump]
  161. quick
  162. max_allowed_packet = 32M

初始化

  1. cd /usr/local/mysql8-1/bin
  3. ./mysqld --defaults-file=/usr/local/mysql8-1/etc/my.cnf --initialize  --user=mysql
  5. # 安全启动
  6. ./mysqld_safe --defaults-file=/usr/local/mysql8-1/etc/my.cnf &
  8. mysql -uroot -S /usr/local/mysql8-1/mysql.sock -p
  9. alter user 'root'@'localhost' identified by 'root@123';
  10. flush privileges;

—initialize：在日志文件打印一个随机密码

—initialize-insecure：不会产生随机密码，第一次登陆数据库使用空密码

实例2

修改配置文件的目录结构和服务监听端口即可

复制文件&创建配置文件

  1. cp -r mysql-8.0.19-linux-glibc2.12-x86_64/ mysql8-2
  3. mkdir /usr/local/mysql8-2/data
  4. mkdir /usr/local/mysql8-2/etc
  5. chown -R mysql:mysql /usr/local/mysql8-2/

/usr/local/mysql8-2/etc/my.cnf

  1. [client]
  2. port    = 3506
  3. socket  = /usr/local/mysql8-2/mysql.sock
  5. [mysql]
  6. prompt="\u@db \R:\m:\s [\d]> "
  7. no-auto-rehash
  9. [mysqld]
  10. server-id = 3306100
  11. user    = mysql
  12. port    = 3506
  13. basedir = /usr/local/mysql8-2
  14. datadir = /usr/local/mysql8-2/data
  15. socket  = /usr/local/mysql8-2/mysql.sock
  16. pid-file = db.pid
  18. character-set-server = utf8mb4
  20. skip_name_resolve = 1
  21. open_files_limit    = 65535
  22. back_log = 1024
  23. max_connections = 512
  24. max_connect_errors = 1000000
  25. table_open_cache = 1024
  26. table_definition_cache = 1024
  27. table_open_cache_instances = 64
  28. thread_stack = 512K
  29. external-locking = FALSE
  30. max_allowed_packet = 32M
  31. sort_buffer_size = 4M
  32. join_buffer_size = 4M
  33. thread_cache_size = 768
  35. interactive_timeout = 600
  36. wait_timeout = 600
  37. tmp_table_size = 32M
  38. max_heap_table_size = 32M
  39. slow_query_log = 1
  40. slow_query_log_file = /usr/local/mysql8-2/data/slow.log
  41. log-error = /usr/local/mysql8-2/data/error.log
  42. long_query_time = 0.1
  44. log-bin = /usr/local/mysql8-2/data/mysql-binlog
  45. sync_binlog = 1
  46. binlog_cache_size = 4M
  47. max_binlog_cache_size = 1G
  48. max_binlog_size = 1G
  49. gtid_mode = on
  50. enforce_gtid_consistency = 1
  51. log_slave_updates
  52. binlog_format = row
  53. relay_log_recovery = 1
  54. relay-log-purge = 1
  55. key_buffer_size = 32M
  56. read_buffer_size = 8M
  57. read_rnd_buffer_size = 4M
  58. bulk_insert_buffer_size = 64M
  59. #myisam_sort_buffer_size = 128M
  60. #myisam_max_sort_file_size = 10G
  61. #myisam_repair_threads = 1
  62. lock_wait_timeout = 3600
  63. explicit_defaults_for_timestamp = 1
  64. innodb_thread_concurrency = 0
  65. innodb_sync_spin_loops = 100
  66. innodb_spin_wait_delay = 30
  67. master_info_repository = TABLE
  68. relay_log_info_repository = TABLE
  69. slave_parallel_type=LOGICAL_CLOCK
  71. transaction_isolation = REPEATABLE-READ
  72. #innodb_additional_mem_pool_size = 16M
  73. innodb_buffer_pool_size = 1024M
  74. innodb_buffer_pool_instances = 8
  75. innodb_buffer_pool_load_at_startup = 1
  76. innodb_buffer_pool_dump_at_shutdown = 1
  77. innodb_data_file_path = ibdata1:1G:autoextend
  78. innodb_flush_log_at_trx_commit = 1
  79. innodb_log_buffer_size = 32M
  80. innodb_log_file_size = 2G
  81. innodb_log_files_in_group = 2
  82. #innodb_max_undo_log_size = 1G
  84. # 根据您的服务器IOPS能力适当调整
  85. # 一般配普通SSD盘的话，可以调整到 10000 - 20000
  86. # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
  87. innodb_io_capacity = 4000
  88. innodb_io_capacity_max = 8000
  89. innodb_flush_neighbors = 0
  90. innodb_write_io_threads = 8
  91. innodb_read_io_threads = 8
  92. innodb_purge_threads = 4
  93. innodb_page_cleaners = 4
  94. innodb_open_files = 65535
  95. innodb_max_dirty_pages_pct = 50
  96. innodb_flush_method = O_DIRECT
  97. innodb_lru_scan_depth = 4000
  98. innodb_checksum_algorithm = crc32
  99. #innodb_file_format = Barracuda
  100. #innodb_file_format_max = Barracuda
  101. innodb_lock_wait_timeout = 10
  102. innodb_rollback_on_timeout = 1
  103. innodb_print_all_deadlocks = 1
  104. innodb_file_per_table = 1
  105. innodb_online_alter_log_max_size = 4G
  106. innodb_stats_on_metadata = 0
  108. innodb_status_file = 1
  109. # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
  110. innodb_status_output = 0
  111. innodb_status_output_locks = 0
  113. #performance_schema
  114. performance_schema = 1
  115. performance_schema_instrument = '%=on'
  117. #innodb monitor
  118. innodb_monitor_enable="module_innodb"
  119. innodb_monitor_enable="module_server"
  120. innodb_monitor_enable="module_dml"
  121. innodb_monitor_enable="module_ddl"
  122. innodb_monitor_enable="module_trx"
  123. innodb_monitor_enable="module_os"
  124. innodb_monitor_enable="module_purge"
  125. innodb_monitor_enable="module_log"
  126. innodb_monitor_enable="module_lock"
  127. innodb_monitor_enable="module_buffer"
  128. innodb_monitor_enable="module_index"
  129. innodb_monitor_enable="module_ibuf_system"
  130. innodb_monitor_enable="module_buffer_page"
  131. innodb_monitor_enable="module_adaptive_hash"
  133.  # Group Replication
  134. #server_id = 1003306
  135. #gtid_mode = ON
  136. #enforce_gtid_consistency = ON
  137. #master_info_repository = TABLE
  138. #relay_log_info_repository = TABLE
  139. binlog_checksum = NONE
  140. #log_slave_updates = ON
  141. #log_bin = binlog
  142. #binlog_format= ROW
  143. transaction_write_set_extraction = XXHASH64
  145. # expire_logs_days参数未来可能被弃用的变量，会出现告警信息
  146. binlog_expire_logs_seconds = 7
  148. # 8版本弃用的变量
  149. # loose-group_replication_group_name = 'e842862c-9b12-11e8-8131-080027f1fd08'
  150. # internal_tmp_disk_storage_engine = InnoDB # 8版本不再支持，默认引擎
  151. # query_cache_size = 0 # 8版本不再支持这两个参数
  152. # query_cache_type = 0
  153. # loose-group_replication_start_on_boot = off
  154. # loose-group_replication_local_address = 'enmoedu:33066'
  155. # loose-group_replication_group_seeds ='enmoedu1:33067,enmoedu2:33068,enmoedu:33066'
  156. # loose-group_replication_bootstrap_group = off
  157. # loose-group_replication_single_primary_mode=off
  158. # loose-group_replication_enforce_update_everywhere_checks=true
  160. [mysqldump]
  161. quick
  162. max_allowed_packet = 32M

初始化

  1. cd /usr/local/mysql8-2/bin
  3. ./mysqld --defaults-file=/usr/local/mysql8-2/etc/my.cnf --initialize  --user=mysql
  5. # 安全启动
  6. ./mysqld_safe --defaults-file=/usr/local/mysql8-2/etc/my.cnf &
  8. mysql -uroot -S /usr/local/mysql8-2/mysql.soc -p
  9. alter user 'root'@'localhost' identified by 'root@123';
  10. flush privileges;
  12. # 关闭数据库
  13. ./mysqladmin shutdown -uroot -p'password'

主从搭建

主从原理

主从复制是依赖于 binlog 二进制日志，需要注意的是：

  • binlog 是属于 server 层次，各个引擎都有
  • redo log，undo log属于Innodb 引擎层次，用于保证事务性的

主从复制原理：

  • master 上的 binlogdump 线程，在 slave 正常连接的情况下，会将 binlog 二进制日志发送给 slave
  • binlog 二进制日志中记录了 master 上所有的更新操作
  • slave 上有个 I/O 线程，通过读取 master 上的 binlog ，写到自己的中继日志（delay log ）中
  • salve 上有个 SQL 线程，通过重放 delay log 里的内容，来实现同步，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储， 从而实现将改变反映到它自己的数据(数据重放)。

注意事项：

  • 主从服务器操作系统版本和位数一致；
  • Master 和 Slave 数据库的版本要一致；
  • Master 和 Slave 数据库中的数据要一致；
  • Master 开启二进制日志， Master 和 Slave 的 server_id 在局域网内必须唯一；

简要配置：

  • master
  1. 修改数据库配置文件， 指明 server_id， 开启二进制日志(log-bin)；
  2. 启动数据库， 查看当前是哪个日志， position 号是多少；
  3. 登录数据库， 授权数据复制用户（IP 地址为从机 IP 地址， 如果是双向主从， 这里的还需要授权本机的 IP 地址， 此时自己的 IP 地址就是从 IP 地址)；
  4. 备份数据库（记得加锁和解锁）；
  5. 传送备份数据到 Slave 上；
  6. 启动数据库；
  • salve
  1. 修改数据库配置文件， 指明 server_id， 开启二进制日志(log-bin)；
  2. 启动数据库， 还原备份；
  3. 查看当前是哪个日志， position 号是多少（单向主从此步不需要， 双向主从需要）；
  4. 指定 Master 的地址、 用户、 密码等信息；
  5. 开启同步， 查看状态。

规划

目录结构 端口 server_id
master /usr/local/mysql8-1/ 3406 3306100
slave /usr/local/mysql8-2/ 3506 3506100

单主机多实例实现主从 -

配置

  1. 检查master节点是否开启二进制日志文件（log-bin）
  2. 检查server_id

没有的添加配置

创建复制操作用户-repl

master

在主节点创建一个用户repl，用于从节点链接主节点时使用。 
  1. # 创建用户
  2. mysql> CREATE USER 'repl'@'135.129.12.28' IDENTIFIED WITH mysql_native_password BY 'repl@123';
  3. # 授权
  4. mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'135.129.12.28';
  6. mysql> flush privileges;
  8. # 获取gtid
  9. mysql> show variables like '%gtid%';

localhost根据实际IP

slave

  1. # 设置为master的gtid
  2. SET @@GLOBAL.GTID_PURGED = '08d09f71-c9c2-11eb-8bca-005056b0ed50:1-15';
  4. # change master to master_host = '135.129.12.28',master_port = 3306,master_user = 'repl',master_password = 'repl@123',master_auto_position=1;

MASTER_HOST # master服务IP

Master_Port # master服务端口

MASTER_USER # master复制用户

MASTER_PASSWORD # master复制用户密码

master_auto_position # 1 自动获取之前读取的位置

开启同步

master

  1. # 备份整个数据库 到 slave恢复
  2. mysqldump -uroot -p -h127.0.0.1 -P3306 --opt --hex-blob --single_transaction -R --default-character-set=utf8 --master-data=2 --all-databases>/temp/dbfull3306.sql

slave

  1. # 开启同步
  2. mysql> start slave;
  4. # 查看同步状态
  5. mysql> show slave status\G;

Slave_IO_State值以下表示连接到master

Waiting for master to send event

MySQL服务部署 - 图1

测试

master上创建数据库，查看salve上是否同步到数据

MySQL服务部署 - 图2MySQL服务部署 - 图3

取消主从

slave

  1. mysql>stop slave;
  3. QueryOK, 0 rowsaffected (0,00 sec)
  5. mysql>reset slave all;
  6. # or
  7. mysql>reset master;
  9. QueryOK, 0 rowsaffected (0,04 sec)
  11. mysql> show slave status\G
  13. Emptyset (0,00 sec)
  16. 此时真正实现了清除slave同步复制关系！

级联复制

A -> B -> C

步骤和上面一样