Replication基础(一) MySQL数据库单机多实例安装

最新推荐文章于 2024-03-17 11:43:57 发布

sunashe

最新推荐文章于 2024-03-17 11:43:57 发布

阅读量545

点赞数 1

分类专栏： MySQL-Replication 文章标签： MySQL

欢迎转载，但请注明出处。

本文链接：https://blog.youkuaiyun.com/sun_ashe/article/details/82118354

版权

MySQL-Replication 专栏收录该内容

17 篇文章

订阅专栏

本文适用于Linux操作系统，介绍了MySQL数据库的安装与启动。以MySQL - 5.7.23为例，说明了数据库目录设置，包括base目录、data目录等，还给出配置文件内容示例。阐述了初始化数据库的方法，以及使用mysqld_safe方式启动数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

此文章适用于linux操作系统，win的话，没怎么玩过呢。

数据库版本

其实没太大的差别，比如说目前最新的MySQL-5.7.23,使用二进制安装包文件进行安装。
官网下载，如下：
image_1cltjcsa01km28p04dr43iget9.png-419.9kB

ashe@ubuntu:/data/mysql/base$ ll
总用量 68
drwxrwxr-x 11 ashe ashe  4096 8月  22 16:54 ./
drwxrwxr-x  5 ashe ashe  4096 8月  22 16:59 ../
drwxrwxr-x  2 ashe ashe  4096 8月  22 13:44 bin/
-rw-r--r--  1 ashe ashe 17987 8月  21 18:50 COPYING
drwxr-x---  5 ashe ashe  4096 8月  22 16:54 data/
drwxr-xr-x  2 ashe ashe  4096 8月  22 13:43 docs/
drwxrwxr-x  3 ashe ashe  4096 8月  22 13:43 include/
drwxrwxr-x  4 ashe ashe  4096 8月  22 13:44 lib/
drwxrwxr-x  3 ashe ashe  4096 8月  21 19:40 man/
drwxr-xr-x 10 ashe ashe  4096 8月  22 13:44 mysql-test/
-rw-r--r--  1 ashe ashe  2478 8月  21 18:50 README
drwxrwxr-x 28 ashe ashe  4096 8月  21 19:41 share/
drwxrwxr-x  2 ashe ashe  4096 8月  21 19:41 support-files/
ashe@ubuntu:/data/mysql/base$ pwd
/data/mysql/base

data目录

ashe@ubuntu:/data/mysql$ ll
总用量 36
drwxrwxr-x  5 ashe ashe 4096 8月  22 16:59 ./
drwxr-xr-x  3 ashe ashe 4096 8月  22 12:57 ../
drwxrwxr-x 11 ashe ashe 4096 8月  22 16:54 base/
drwxrwxr-x  6 ashe ashe 4096 8月  22 16:55 data/
-rw-rw-r--  1 ashe ashe 3966 8月  22 16:57 my.cnf.13307
-rw-rw-r--  1 ashe ashe 3966 8月  22 16:58 my.cnf.13308
-rw-rw-r--  1 ashe ashe 3966 8月  22 16:58 my.cnf.13309
-rw-rw-r--  1 ashe ashe 3966 8月  22 16:59 my.cnf.13310
drwxrwxr-x  6 ashe ashe 4096 8月  22 16:56 mysql_tmp/
ashe@ubuntu:/data/mysql$ pwd
/data/mysql
ashe@ubuntu:/data/mysql$

多实例可以共用base目录，data目录按照数据库实例端口号区分，如下

ashe@ubuntu:/data/mysql$ cd data/
ashe@ubuntu:/data/mysql/data$ ll
总用量 24
drwxrwxr-x 6 ashe ashe 4096 8月  22 16:55 ./
drwxrwxr-x 5 ashe ashe 4096 8月  22 16:59 ../
drwxr-x--- 7 ashe ashe 4096 8月  24 20:31 13307/
drwxr-x--- 6 ashe ashe 4096 8月  24 20:31 13308/
drwxr-x--- 6 ashe ashe 4096 8月  24 10:24 13309/
drwxr-x--- 6 ashe ashe 4096 8月  24 10:24 13310/
ashe@ubuntu:/data/mysql/data$ pwd
/data/mysql/data
ashe@ubuntu:/data/mysql/data$

配置文件

配置文件放在/data/mysql/，后缀以端口号区分，如下：

ashe@ubuntu:/data/mysql$ ll
总用量 36
drwxrwxr-x  5 ashe ashe 4096 8月  22 16:59 ./
drwxr-xr-x  3 ashe ashe 4096 8月  22 12:57 ../
drwxrwxr-x 11 ashe ashe 4096 8月  22 16:54 base/
drwxrwxr-x  6 ashe ashe 4096 8月  22 16:55 data/
-rw-rw-r--  1 ashe ashe 3966 8月  22 16:57 my.cnf.13307
-rw-rw-r--  1 ashe ashe 3966 8月  22 16:58 my.cnf.13308
-rw-rw-r--  1 ashe ashe 3966 8月  22 16:58 my.cnf.13309
-rw-rw-r--  1 ashe ashe 3966 8月  22 16:59 my.cnf.13310
drwxrwxr-x  6 ashe ashe 4096 8月  22 16:56 mysql_tmp/
ashe@ubuntu:/data/mysql$ pwd
/data/mysql

MySQL tmp目录

临时目录最好也分开保存，如下

ashe@ubuntu:/data/mysql$ cd mysql_tmp
ashe@ubuntu:/data/mysql/mysql_tmp$ pwd
/data/mysql/mysql_tmp
ashe@ubuntu:/data/mysql/mysql_tmp$ ll
总用量 24
drwxrwxr-x 6 ashe ashe 4096 8月  22 16:56 ./
drwxrwxr-x 5 ashe ashe 4096 8月  22 16:59 ../
drwxrwxr-x 2 ashe ashe 4096 8月  24 13:41 13307/
drwxrwxr-x 2 ashe ashe 4096 8月  24 13:41 13308/
drwxrwxr-x 2 ashe ashe 4096 8月  22 16:59 13309/
drwxrwxr-x 2 ashe ashe 4096 8月  22 16:59 13310/
ashe@ubuntu:/data/mysql/mysql_tmp$

其他各个目录也可以单独拿出来存放，比如共享表空间文件等，但是放在数据库的datadir中也可以。

配置文件内容

当然可以酌情修改，这个不影响安装。如下是以13307端口实例为例的配置文件，13308/13309/13310其他实例的只需要修改配置文件即可。

[mysql]
# DO NOT CHANGE
port = 13307
default-character-set = utf8mb4
[mysqld]
# DO NOT CHANGE
server_id = 12713307
basedir = /data/mysql/base
datadir = /data/mysql/data/13307
socket = /tmp/mysql_13307.sock
port = 13307
log-bin = mysql-bin
tmpdir = /data/mysql/mysql_tmp/13307
skip-name-resolve = 1
max_allowed_packet = 64M
read_only = 0
default_storage_engine = InnoDB
character_set_server = utf8mb4
skip-external-locking = 1
table_open_cache_instances = 32
back_log = 1500
wait_timeout = 3600
interactive_timeout = 3600
default-time-zone = '+8:00'
explicit_defaults_for_timestamp = 1
lower_case_table_names = 1
symbolic-links = 0
secure_file_priv = ''
# INNODB #
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 1
innodb_stats_on_metadata = 0
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
#innodb_log_file_size = 2048M
#innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_max_dirty_pages_pct = 60
#innodb_data_file_path = ibdata1:1G:autoextend
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 4
innodb_flush_log_at_trx_commit = 1
innodb_adaptive_flushing = 1
innodb_thread_concurrency = 0
innodb_stats_persistent = 1
innodb_purge_threads = 4
innodb_use_native_aio = 1
innodb_autoinc_lock_mode = 2
innodb_change_buffering = inserts
innodb_read_io_threads = 16
innodb_write_io_threads = 16
#innodb_buffer_pool_load_at_startup = 1
#innodb_buffer_pool_dump_at_shutdown = 1
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 64M
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 6
innodb_strict_mode = 1
innodb_online_alter_log_max_size = 1G
innodb_stats_persistent_sample_pages = 64
innodb_large_prefix = 1
# ADD SSD #
#innodb_lru_scan_depth = 4096
#innodb_flush_neighbors = 0
# SSD=8000 SAS=2000 #
innodb_io_capacity = 2000
innodb_io_capacity_max = 20000
# REPLICATION #
binlog_row_image = FULL
binlog_checksum = CRC32
master_verify_checksum = on
slave_sql_verify_checksum = on
binlog_format = row
relay-log = mysql-relay-bin
log-slave-updates = 1
skip_slave_start = 1
expire_logs_days = 14
slave_net_timeout = 30
sync_binlog = 1
relay-log-info-repository = TABLE
relay_log_recovery = 1
master_info_repository = TABLE
#sync-master-info = 1
#sync_relay_log_info = 1
#sync_relay_log = 1
binlog_rows_query_log_events = 1
binlog_gtid_simple_recovery = 1
replicate_wild_ignore_table = mysql.backup_%
replicate-ignore-table = mysql.ibbackup_binlog_marker
#slave-skip-errors = ddl_exist_errors
log-bin-trust-function-creators = 1
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
# CACHES AND LIMITS #
key_buffer_size = 32M
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 4096
query_cache_type = 0
query_cache_size = 0
max_connections = 5000
thread_cache_size = 1024
open_files_limit = 65535
join_buffer_size = 32M
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 8M
binlog_cache_size = 1M
# LOGGING #
# DO NOT CHANGE
log_error = mysql-error.log
slow_query_log = 1
slow_query_log_file = mysql-slow.log
long_query_time = 2
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# SEMISYNC #
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#rpl-semi-sync-master-enabled = 1
#rpl-semi-sync-slave-enabled = 1
#rpl_semi_sync_master_timeout = 5000
# GTID #
gtid-mode = ON
enforce-gtid-consistency = 1
# mysql5.7 #
# new innodb settings #
innodb_page_cleaners = 16
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
#innodb_purge_rseg_truncate_frequency = 128
innodb_buffer_pool_dump_pct = 40
# new replication settings #
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
slave_preserve_commit_order = 1
slave_transaction_retries = 128
# other change #
log_timestamps = system
show_compatibility_56 = on
sql_mode = NO_ENGINE_SUBSTITUTION
optimizer_switch='index_merge_intersection=off,use_index_extensions=off'
slave_pending_jobs_size_max = 128M

初始化数据库

#初始化13307实例
ashe@ubuntu:/data/mysql/base$ cd /data/mysql/base
ashe@ubuntu:/data/mysql/base$ ./bin/mysqld --initialize-insecure --basedir=./ --datadir=/data/mysql/data/13307 --user=ashe
#初始化13308实例
ashe@ubuntu:/data/mysql/base$ cd /data/mysql/base
ashe@ubuntu:/data/mysql/base$ ./bin/mysqld --initialize-insecure --basedir=./ --datadir=/data/mysql/data/13308 --user=ashe
#初始化13309实例
ashe@ubuntu:/data/mysql/base$ cd /data/mysql/base
ashe@ubuntu:/data/mysql/base$ ./bin/mysqld --initialize-insecure --basedir=./ --datadir=/data/mysql/data/13309 --user=ashe
#初始化13310实例
ashe@ubuntu:/data/mysql/base$ cd /data/mysql/base
ashe@ubuntu:/data/mysql/base$ ./bin/mysqld --initialize-insecure --basedir=./ --datadir=/data/mysql/data/13309 --user=ashe

启动数据库

数据库启动有很多中方式，可以使用mysqld直接启动，也可以使用mysqld_safe守护进程的方式启动，也可以加入到系统服务，通过service启动，这里只介绍通过mysqld_safe的方式进行启动

#启动13307实例
ashe@ubuntu:/data/mysql/base$ cd /data/mysql/base
ashe@ubuntu:/data/mysql/base$ ./bin/mysqld_safe --defaults-file=../my.cnf.13307 --user=ashe &
[1] 5890
ashe@ubuntu:/data/mysql/base$ 2018-08-27T12:02:22.685391Z mysqld_safe Logging to '/data/mysql/data/13307/mysql-error.log'.
2018-08-27T12:02:22.715197Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data/13307

#启动13308实例
ashe@ubuntu:/data/mysql/base$ ./bin/mysqld_safe --defaults-file=../my.cnf.13308 --user=ashe &
[2] 7289
ashe@ubuntu:/data/mysql/base$ 2018-08-27T12:02:40.261877Z mysqld_safe Logging to '/data/mysql/data/13308/mysql-error.log'.
2018-08-27T12:02:40.286863Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data/13308

#启动13309实例
ashe@ubuntu:/data/mysql/base$ ./bin/mysqld_safe --defaults-file=../my.cnf.13309 --user=ashe &
[3] 8687
ashe@ubuntu:/data/mysql/base$ 2018-08-27T12:03:07.769981Z mysqld_safe Logging to '/data/mysql/data/13309/mysql-error.log'.
2018-08-27T12:03:07.795197Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data/13309

#启动13310实例
ashe@ubuntu:/data/mysql/base$ ./bin/mysqld_safe --defaults-file=../my.cnf.13310 --user=ashe &
[4] 10089
ashe@ubuntu:/data/mysql/base$ 2018-08-27T12:03:23.917362Z mysqld_safe Logging to '/data/mysql/data/13310/mysql-error.log'.
2018-08-27T12:03:23.942484Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data/13310

如果没有报错，则4个实例就启动成功了，查看进程

ashe@ubuntu:/data/mysql/base$ ps -ef|grep mysqld
ashe       5890   5804  0 20:02 pts/17   00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=../my.cnf.13307 --user=ashe
ashe       7236   5890  0 20:02 pts/17   00:00:00 /data/mysql/base/bin/mysqld --defaults-file=../my.cnf.13307 --basedir=/data/mysql/base --datadir=/data/mysql/data/13307 --plugin-dir=/data/mysql/base/lib/plugin --log-error=/data/mysql/data/13307/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/data/13307/ubuntu.pid --socket=/tmp/mysql_13307.sock --port=13307
ashe       7289   5804  0 20:02 pts/17   00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=../my.cnf.13308 --user=ashe
ashe       8634   7289  1 20:02 pts/17   00:00:01 /data/mysql/base/bin/mysqld --defaults-file=../my.cnf.13308 --basedir=/data/mysql/base --datadir=/data/mysql/data/13308 --plugin-dir=/data/mysql/base/lib/plugin --log-error=/data/mysql/data/13308/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/data/13308/ubuntu.pid --socket=/tmp/mysql_13308.sock --port=13308
ashe       8687   5804  0 20:03 pts/17   00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=../my.cnf.13309 --user=ashe
ashe      10034   8687  2 20:03 pts/17   00:00:01 /data/mysql/base/bin/mysqld --defaults-file=../my.cnf.13309 --basedir=/data/mysql/base --datadir=/data/mysql/data/13309 --plugin-dir=/data/mysql/base/lib/plugin --log-error=/data/mysql/data/13309/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/data/13309/ubuntu.pid --socket=/tmp/mysql_13309.sock --port=13309
ashe      10089   5804  0 20:03 pts/17   00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=../my.cnf.13310 --user=ashe
ashe      11436  10089  4 20:03 pts/17   00:00:02 /data/mysql/base/bin/mysqld --defaults-file=../my.cnf.13310 --basedir=/data/mysql/base --datadir=/data/mysql/data/13310 --plugin-dir=/data/mysql/base/lib/plugin --log-error=/data/mysql/data/13310/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/data/13310/ubuntu.pid --socket=/tmp/mysql_13310.sock --port=13310
ashe      11492   5804  0 20:04 pts/17   00:00:00 grep --color=auto mysqld

可以看到有4个mysqld和4个mysqld_safe进程。

结束语

非常简单，没什么好说的。
对了，初始化参数--initialize-insecure意思是初始化用户没有密码，所以，数据库启动后，可以使用如下方式链接

#链接13307，其他实例同理。
ashe@ubuntu:/data/mysql/base$ mysql -S /tmp/mysql_13307.sock  -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-debug-log Source distribution

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> show databases;