基于YUM安装与源码编译或二进制多实例安装Mariadb.mysql

发表于 更新于 分类于 阅读次数： 本文字数： 10k 阅读时长 ≈ 9 分钟

基于YUM

  • 1 安装
1
yum install mariadb
  • 2 创建多实例对应的目录结构
1
2
mkdir /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid} -pv
chown -R mysql.mysql /mysql
  • 3 创建多实例的数据库文件
1
2
3
4
5
 mysql_install_db --datadir=/mysql/3306/data/ --user=mysql
 mysql_install_db --datadir=/mysql/3307/data/ --user=mysql
 mysql_install_db --datadir=/mysql/3308/data/ --user=mysql
````
* 4 创建对应配置文件

cp /etc/my.cnf /mysql/3306/etc
vim /mysql/3306/etc/my.cnf
[mysqld]
port=3306 加一行
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
* ```
  cp /mysql/3306/etc/my.cnf  /mysql/3307/etc/my.cnf   
  /mysql/3307/etc/my.cnf 修改
  cp /mysql/3306/etc/my.cnf  /mysql/3308/etc/my.cnf   
  /mysql/3308/etc/my.cnf 修改
  
  * 5 准备各实例的启动脚本
    vi /mysql/{3306,3307,3308}/bin/mysqld 
    cat /mysq/3306/bin/mysqld 
    #!/bin/bash
    port=3306
    mysql_user="root"
    mysql_pwd="centos"
    cmd_path="/usr/bin"
    mysql_basedir="/mysql"
    mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
  
  function_start_mysql()
  {
      if [ ! -e "$mysql_sock" ];then
        printf "Starting MySQL...\n"
        ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
      else
        printf "MySQL is running...\n"
        exit
      fi
  }
  
  
  function_stop_mysql()
  {
      if [ ! -e "$mysql_sock" ];then
         printf "MySQL is stopped...\n"
         exit
      else
         printf "Stoping MySQL...\n"
         ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
     fi
  }
  
  
  function_restart_mysql()
  {
      printf "Restarting MySQL...\n"
      function_stop_mysql
      sleep 2
      function_start_mysql
  }
  
  case $1 in
  start)
      function_start_mysql
  ;;
  stop)
      function_stop_mysql
  ;;
  restart)
      function_restart_mysql
  ;;
  *)
      printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
  esac
  
  * 改权限
    chmod +x /mysql/{3306,3307,3308}/bin/mysqld 
  
  * 6 启动和关闭实例
    /mysql/{3306,3307,3308}/bin/mysqld start 
    /mysql/{3306,3307,3308}/bin/mysqld stop  
  
  * 7 测试连接
    mysql -S /mysql/{3306,3307,3308}/socket/mysql.sock
  
  * 8 安全加固
    mysqladmin  -S /mysql/{3306,3307,3308}/socket/mysql.sock   password 'centos'
    vi /mysql/{3306,3307,3308}/bin/mysqld  加上对应centos口令

二进制的安装

1.先创建mysql用户及mysql组，并制定家目录为/data/mysql

1
2
groupadd -r -g 306 mysql #指定属组gid为306
useradd -r -g 306 -u 306 -d /data/mysql mysql #指定属主uid为306，家目录为/data/mysql

2.准备数据目录(mysq用户家目录),并修正权限

1
mkdir /data/mysql -p ;chown mysql:mysql /data/mysql

3.去官网下载mariadb 二进制tar包（链接是CentOS7X86_64的10.4.13稳定版）
4.解压tar包指/usr/local目录下,递归改属主为root、属组为mysql，并在/usr/local目录下创建一个名为mysql的软链接指向解压好的mariadb目录

1
2
3
4
tar xzf mariadb-10.4.13-linux-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -sv mariadb-10.4.13-linux-x86_64 mysql
chown -R root:mysql /usr/local/mysql/

5.创建配置文件,并修改

1
2
3
4
mkdir /etc/mysql/
cp /etc/my.cnf /etc/mysql/my.cnf
sed -ri '/datadir=/s@(.*=).*@\1/data/mysql@' /etc/mysql/my.cnf #修改配置文件，指定数据库储存路径
sed -ri '/datadir/a\innodb_file_per-table=on\nskip_name_resolve=on' /etc/mysql/my.cnf #设置每个表独立文件 和 禁用主机名解析

6.创建数据库文件

1
/usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysql --user=mysql

7.创建服务脚本并启动服务

1
2
cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld

8.增加PATH环境变量路径,并生效。

1
2
echo 'PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

9 启动数据库

1
systemctl start mysqld

10.运行安全初始化脚本，设置root口令、禁用匿名登陆、禁用远程主机登陆、删除test数据库，并立即生效(根据提示操作)。

1
2
ln -s /var/lib/mysql/mysql.sock /tmp
/usr/local/mysql/bin/mysql_secure_installation

至此，二进制安装mariadb数据库就完成了

源码编译安装MySQL

1
2
/usr/sbin/groupadd -g 366 -r mysql
/usr/sbin/useradd -c "MySQL" -u 366 -g mysql -s /sbin/nologin -r -d /data/mysql mysql
  • 准备数据库目录
1
2
mkdir /data/mysql -pv
 chown mysql.mysql /data/mysql
  • 准备源码文件
1
2
tar xf mariadb-10.2.23.tar.gz
cd mariadb-10.2.23
  • 预编译
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/mysql \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
  • 编译安装
1
2
make -j 8
make install
  • 导出环境变量
1
2
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
  • 初始化数据库
1
2
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
  • 准备配置文件
1
2
3
4
5
cp support-files/my-innodb-heavy-4G.cnf /etc/my.cnf.d/my.cnf
vim /etc/my.cnf.d/my.cnf   #[mysqld] 配置段中中加入如下内容
datadir = /data/mysql
innodb_file_per_table = on
skip_name_resolve = on
  • 准备服务脚本
1
2
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
  • 启动服务
1
systemctl start mysqld
  • 安全初始化
1
mysql_secure_installation

other

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"
[client]
port    = 3306
socket    = /data/mysql/mysql_3306/tmp/mysql_3306.sock

[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
admin_address = 127.0.0.1
basedir    = /usr/local/mysql
datadir    = /data/mysql/mysql_3306/data
socket    = /data/mysql/mysql_3306/tmp/mysql_3306.sock
pid-file = mysql_3306.pid
character-set-server = utf8mb4
skip_name_resolve = 1


#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

# Two-Master configure
#server-1 
#auto-increment-offset = 1
#auto-increment-increment = 2 

#server-2                          
#auto-increment-offset = 2
#auto-increment-increment = 2


# semi sync replication settings #
#plugin_dir = /usr/local/mysql/lib/mysql/plugin
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

open_files_limit    = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1536
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log
log-error = /data/mysql/mysql_3306/logs/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /data/mysql/mysql_3306/logs/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_expire_logs_seconds=2592000 
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_row_image=FULL
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

#transaction_isolation = REPEATABLE-READ
transaction_isolation = READ-COMMITTED
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2867M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/mysql_3306/undolog
innodb_undo_tablespaces = 95

# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0

# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 2592000
#innodb_dedicated_server = 0

innodb_status_file = 1
# 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
validate_password_policy=LOW

[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535

EOF