数据库安装可以参考
https://blog.csdn.net/u013246891/article/details/81747639
多实例安装可以参考
https://blog.csdn.net/u013246891/article/details/81777526
我下面进行一下填坑
由于MYSQL可以通过service启动，但是按照上述方法安装完后，无法通过service启动，因为service默认读取的是[mysql]属性下的内容，而且只读取my.cnf，所以需要用mysqld_multi进行多实例控制，上述讲解还有一个问题就是navicate无法访问，因为MYSQL8改变了密码保存方式，上面例子中的配置文件需要修改，上面包含的我就不再详述。还有一个问题
创建文件夹
mkdir -p /app/mysql/mysqldata/{3307/{data,tmp,binlog,innodb_ts,innodb_log,undo},backup,scripts}
mkdir -p /app/mysql/mysqldata/{3306/{data,tmp,binlog,innodb_ts,innodb_log,undo},backup,scripts}
配置文件
为了使用mysqld_multi，需要3个配置文件，其中两个是用于初始化，另一个是用于mysqld_multi使用
my3306.cnf
[client]
port = 3306
socket = /app/mariadb/mariadbdata/3306/mysql.sock
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /app/mariadb/mariadbdata/mysqld_multi.log
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
user=mysql
server-id=101
datadir=/app/mysql/mysqldata/3306/data
basedir=/app/mysql/mysqldata/3306
port = 3306
mysqlx_port = 33060
user = mysql
socket = /app/mariadb/mariadbdata/3306/mysql.sock
mysqlx_socket=/app/mariadb/mariadbdata/3306/mysqlx.sock
pid-file = /app/mariadb/mariadbdata/3306/mysql.pid
datadir = /app/mariadb/mariadbdata/3306/data
tmpdir = /app/mariadb/mariadbdata/3306/tmp
open_files_limit = 60000
explicit_defaults_for_timestamp
server-id = 1203306
lower_case_table_names = 1
character-set-server = utf8
federated
#sql_mode=STRICT_TRANS_TABLES
max_connections = 1000
max_connect_errors = 100000
interactive_timeout = 86400
wait_timeout = 86400
back_log=100
default-storage-engine = InnoDB
#*********** Logs related settings ***********
log-bin = /app/mariadb/mariadbdata/3306/binlog/mysql-bin
binlog_cache_size=128M
max_binlog_cache_size=256M
max_binlog_size=1G
long_query_time = 1
log_output = FILE
log-error = /app/mariadb/mariadbdata/3306/mysql-error.log
slow_query_log = 1
slow_query_log_file = /app/mariadb/mariadbdata/3306/slow_statement.log
#log_queries_not_using_indexes
general_log = 0
general_log_file = /app/mariadb/mariadbdata/3306/general_statement.log
#expire-logs-days = 14
binlog_expire_logs_seconds = 1728000
relay-log = /app/mariadb/mariadbdata/3306/binlog/relay-bin
relay-log-index = /app/mariadb/mariadbdata/3306/binlog/relay-bin.index
#****** MySQL Replication New Feature*********
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
#*********** INNODB Specific options ***********
innodb_buffer_pool_size = 32G
transaction-isolation=REPEATABLE-READ
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_data_home_dir = /app/mariadb/mariadbdata/3306/innodb_ts
innodb_data_file_path = /app/mariadb/mariadbdata/3306/innodb_ts/ibdata1:2048M:autoextend
innodb_thread_concurrency = 8
innodb_log_buffer_size = 128M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_undo_tablespaces=3
innodb_undo_directory =/app/mariadb/mariadbdata/3306/undo
innodb_log_group_home_dir = /app/mariadb/mariadbdata/3306/innodb_log
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 70
innodb_flush_method=O_DIRECT
innodb_file_per_table = 1
large-pages
[mysql]
no-auto-rehash
default-character-set=utf8
prompt = (\u@\h) [\d]>\_
my3307.cnf
[client]
port = 3307
socket = /app/mysql/mysqldata/3307/mysql.sock
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
default-authentication-plugin=mysql_native_password
port = 3307
mysqlx_port = 33070
user = mysql
socket = /app/mysql/mysqldata/3307/mysql.sock
mysqlx_socket=/app/mysql/mysqldata/3307/mysqlx.sock
pid-file = /app/mysql/mysqldata/3307/mysql.pid
datadir = /app/mysql/mysqldata/3307/data
tmpdir = /app/mysql/mysqldata/3307/tmp
open_files_limit = 60000
explicit_defaults_for_timestamp
server-id = 1203307
lower_case_table_names = 1
character-set-server = utf8
federated
#sql_mode=STRICT_TRANS_TABLES
max_connections = 1000
max_connect_errors = 100000
interactive_timeout = 86400
wait_timeout = 86400
back_log=100
default-storage-engine = InnoDB
#*********** GTID settting*******************
log_slave_updates = 1
gtid_mode=ON
enforce-gtid-consistency=true
innodb_flush_log_at_trx_commit=2
binlog_format= row
skip-slave-start=1
sync_binlog=5
#*********** Logs related settings ***********
log-bin = /app/mysql/mysqldata/3307/binlog/mysql-bin
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
long_query_time = 1
log_output = FILE
log-error = /app/mysql/mysqldata/3307/mysql-error.log
slow_query_log = 1
slow_query_log_file = /app/mysql/mysqldata/3307/slow_statement.log
#log_queries_not_using_indexes
general_log = 0
general_log_file = /app/mysql/mysqldata/3307/general_statement.log
#expire-logs-days = 14
binlog_expire_logs_seconds = 1728000
relay-log = /app/mysql/mysqldata/3307/binlog/relay-bin
relay-log-index = /app/mysql/mysqldata/3307/binlog/relay-bin.index
#****** MySQL Replication New Feature*********
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
#*********** INNODB Specific options ***********
innodb_buffer_pool_size = 2048M
transaction-isolation=REPEATABLE-READ
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_data_home_dir = /app/mysql/mysqldata/3307/innodb_ts
innodb_data_file_path = /app/mysql/mysqldata/3307/innodb_ts/ibdata1:2048M:autoextend
innodb_thread_concurrency = 8
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_undo_tablespaces=3
innodb_undo_directory =/app/mysql/mysqldata/3307/undo
innodb_log_group_home_dir = /app/mysql/mysqldata/3307/innodb_log
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 70
innodb_flush_method=O_DIRECT
[mysql]
no-auto-rehash
default-character-set=utf8
prompt = (\u@\h) [\d]>\_
创建mysql用户
userdel mysql
useradd mysql
用mysql用户创建目录
mkdir -p /app/mysql/mysqldata/{3307/{data,tmp,binlog,innodb_ts,innodb_log,undo},backup,scripts}
mkdir -p /app/mysql/mysqldata/{3306/{data,tmp,binlog,innodb_ts,innodb_log,undo},backup,scripts}
chown -R mysql:mysql /app/mysql/
用root用户初始化数据库
/usr/sbin/mysqld --defaults-file=/etc/my3307.cnf --initialize-insecure --user=mysql --datadir=/app/mysql/mysqldata/3307/data
/usr/sbin/mysqld --defaults-file=/etc/my3306.cnf --initialize-insecure --user=mysql --datadir=/app/mysql/mysqldata/3306/data
用root用户启动两个数据库
/usr/bin/mysqld_safe --defaults-file=/etc/my3306.cnf --datadir=/app/mysql/mysqldata/3306/data --socket=/app/mysql/mysqldata/3306/mysql.sock --pid-file=/app/mysql/mysqldata/3306/mysql.pid --basedir=/usr --user=mysql --port=3306
/usr/bin/mysqld_safe --defaults-file=/etc/my3307.cnf --datadir=/app/mysql/mysqldata/3307/data --socket=/app/mysql/mysqldata/3307/mysql.sock --pid-file=/app/mysql/mysqldata/3307/mysql.pid --basedir=/usr --user=mysql --port=3307
分别连接两个数据库
mysql -uroot -S /app/mysql/mysqldata/3307/mysql.sock
mysql -uroot -S /app/mysql/mysqldata/3306/mysql.sock
然后对两个实例分别进行权限控制让root用户可以远程访问
安装完第一次的默认密码在
vi /app/mysql/mysqldata/3306/mysql-error.log
2018-09-26T12:08:19.435682Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 7sC8jjc(rk%i
alter user user() identified by "root";
use mysql;
update user set authentication_string = '' where user = 'root';
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
update user set host = '%' where user = 'root';
flush privileges;
my.cnf（此文件包含上述两文件的[mysql]部分，但是要注意不要包含[mysql标签]）
[client]
port = 3307
socket = /app/mysql/mysqldata/3307/mysql.sock
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /app/mysql/mysqldata/mysqld_multi.log
[mysqld3306]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
default-authentication-plugin=mysql_native_password
port = 3306
mysqlx_port = 33060
user = mysql
socket = /app/mysql/mysqldata/3306/mysql.sock
mysqlx_socket=/app/mysql/mysqldata/3306/mysqlx.sock
pid-file = /app/mysql/mysqldata/3306/mysql.pid
datadir = /app/mysql/mysqldata/3306/data
tmpdir = /app/mysql/mysqldata/3306/tmp
open_files_limit = 60000
explicit_defaults_for_timestamp
server-id = 1203306
lower_case_table_names = 1
character-set-server = utf8
federated
#sql_mode=STRICT_TRANS_TABLES
max_connections = 1000
max_connect_errors = 100000
interactive_timeout = 86400
wait_timeout = 86400
back_log=100
default-storage-engine = InnoDB
#*********** GTID settting*******************
log_slave_updates = 1
gtid_mode=ON
enforce-gtid-consistency=true
innodb_flush_log_at_trx_commit=2
binlog_format= row
skip-slave-start=1
sync_binlog=5
#*********** Logs related settings ***********
log-bin = /app/mysql/mysqldata/3306/binlog/mysql-bin
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
long_query_time = 1
log_output = FILE
log-error = /app/mysql/mysqldata/3306/mysql-error.log
slow_query_log = 1
slow_query_log_file = /app/mysql/mysqldata/3306/slow_statement.log
#log_queries_not_using_indexes
general_log = 0
general_log_file = /app/mysql/mysqldata/3306/general_statement.log
#expire-logs-days = 14
binlog_expire_logs_seconds = 1728000
relay-log = /app/mysql/mysqldata/3306/binlog/relay-bin
relay-log-index = /app/mysql/mysqldata/3306/binlog/relay-bin.index
#****** MySQL Replication New Feature*********
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
#*********** INNODB Specific options ***********
innodb_buffer_pool_size = 2048M
transaction-isolation=REPEATABLE-READ
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_data_home_dir = /app/mysql/mysqldata/3306/innodb_ts
innodb_data_file_path = /app/mysql/mysqldata/3306/innodb_ts/ibdata1:2048M:autoextend
innodb_thread_concurrency = 8
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_undo_tablespaces=3
innodb_undo_directory =/app/mysql/mysqldata/3306/undo
innodb_log_group_home_dir = /app/mysql/mysqldata/3306/innodb_log
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 70
innodb_flush_method=O_DIRECT
[mysqld3307]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
default-authentication-plugin=mysql_native_password
port = 3307
mysqlx_port = 33070
user = mysql
socket = /app/mysql/mysqldata/3307/mysql.sock
mysqlx_socket=/app/mysql/mysqldata/3307/mysqlx.sock
pid-file = /app/mysql/mysqldata/3307/mysql.pid
datadir = /app/mysql/mysqldata/3307/data
tmpdir = /app/mysql/mysqldata/3307/tmp
open_files_limit = 60000
explicit_defaults_for_timestamp
server-id = 1203307
lower_case_table_names = 1
character-set-server = utf8
federated
#sql_mode=STRICT_TRANS_TABLES
max_connections = 1000
max_connect_errors = 100000
interactive_timeout = 86400
wait_timeout = 86400
back_log=100
default-storage-engine = InnoDB
#*********** GTID settting*******************
log_slave_updates = 1
gtid_mode=ON
enforce-gtid-consistency=true
innodb_flush_log_at_trx_commit=2
binlog_format= row
skip-slave-start=1
sync_binlog=5
#*********** Logs related settings ***********
log-bin = /app/mysql/mysqldata/3307/binlog/mysql-bin
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
long_query_time = 1
log_output = FILE
log-error = /app/mysql/mysqldata/3307/mysql-error.log
slow_query_log = 1
slow_query_log_file = /app/mysql/mysqldata/3307/slow_statement.log
#log_queries_not_using_indexes
general_log = 0
general_log_file = /app/mysql/mysqldata/3307/general_statement.log
#expire-logs-days = 14
binlog_expire_logs_seconds = 1728000
relay-log = /app/mysql/mysqldata/3307/binlog/relay-bin
relay-log-index = /app/mysql/mysqldata/3307/binlog/relay-bin.index
#****** MySQL Replication New Feature*********
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
#*********** INNODB Specific options ***********
innodb_buffer_pool_size = 2048M
transaction-isolation=REPEATABLE-READ
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_data_home_dir = /app/mysql/mysqldata/3307/innodb_ts
innodb_data_file_path = /app/mysql/mysqldata/3307/innodb_ts/ibdata1:2048M:autoextend
innodb_thread_concurrency = 8
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_undo_tablespaces=3
innodb_undo_directory =/app/mysql/mysqldata/3307/undo
innodb_log_group_home_dir = /app/mysql/mysqldata/3307/innodb_log
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 70
innodb_flush_method=O_DIRECT
[mysql]
no-auto-rehash
default-character-set=utf8
prompt = (\u@\h) [\d]>\_
通过如下指令查找进程编号
ps -ef | grep mysql
kill进程
使用mysqld_multi对多实例进行管理控制
mysqld_multi start
官方操作手册
https://dev.mysql.com/doc/refman/8.0/en/mysqld-multi.html
处理
Could not open /app/mysql/mysqldata/3306/mysql-error.log file for error logging: Permission denied.
setenforce 0
扫一扫
354
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
