mariadb-10.5.1二进制安装
一、本博文内容简介：
有关mariaDB的介绍，大家可以移步到百度百科或者mariaDB官网进行查看。
本文主要是介绍mariaDB二进制包如何进行安装，以及mariaDB服务安装后的如何启动以及账户安全设置等等
mariaDB截止2020-02-24日，目前发部的最新的二进制版本是mariadb-10.5.1-linux-glibc_214-x86_64.tar.gz。本文采用这个版本的二进制安装。
从事西部信息中心，服务器租用，云主机，网页空间，国际域名空间，CDN，网络代维等服务。
二、二进制安装mariadb：
官方二进制安装文档https://mariadb.com/kb/en/installing-mariadb-binary-tarballs/
下载地址
https://downloads.mariadb.com/MariaDB/mariadb-10.5/bintar-linux-glibc_214-x86_64/mariadb-10.5.1-linux-glibc_214-x86_64.tar.gz创建数据目录：
useradd MySQL -s /sbin/nologin -M
mkdir /data/mysql/mysql3306/{data,binlog,logs,tmp} -p
cd /data/mysql/;chown -R mysql.mysql mysql3306安装相关依赖和下载解压：
yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 libedit -y
cd /root/
wget https://downloads.mariadb.com/MariaDB/mariadb-10.5/bintar-linux-glibc_214-x86_64/mariadb-10.5.1-linux-glibc_214-x86_64.tar.gz
tar xf mariadb-10.5.1-linux-glibc_214-x86_64.tar.gz -C /usr/local/
cd /usr/local/;ln -sv xf mariadb-10.5.1-linux-glibc_214-x86_64.tar.gz mysql初始化命令：
cd /usr/local/mysql ;touch .my.cnf
./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --defaults-file=./.my.cnfmariaDB启动：
启动方式一： /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
启动方式二：cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql; chmod +x /etc/init.d/mysql; /etc/init.d/mysql start| service mysql start
启动方式三：cp support-files/systemd/mariadb.service /usr/lib/systemd/system/mariadb.service; systemctl start mariadb重要提示：
安装过程中采用的是mysql5.7.24的my.cnf配置文件(my.cnf配置文件参数会附在博文最后)
mariadb-10.5.1在初始化完成后启动报错，发现my.cnf配置文件中以下参数是不支持的：
[root@mgr01 logs]# grep 'unknown variable' error.log
2020-02-23 11:11:35 0 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'log_timestamps=system'
2020-02-23 11:15:17 0 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'slave-preserve-commit-order=ON'
2020-02-23 11:16:29 0 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'gtid_mode=on'
2020-02-23 11:17:11 0 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'enforce_gtid_consistency=on'
2020-02-23 11:17:30 0 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'binlog_group_commit_sync_delay=100'
2020-02-23 11:17:55 0 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'binlog_group_commit_sync_no_delay_count=10'
2020-02-24 5:06:41 0 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'slave_parallel_type=logical_clock'三、账户密码设置、修改和找回：
安装完成默认是没密码的
采用下面设置密码：
ALTER USER 'root'@'localhost' IDENTIFIED BY '654321';采用下面方式修改MariaDB线上的账户的密码:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');不支持下面的命令修改密码：
update mysql.user set authentication_string=password('123456') where user='root' and host='localhost';
update mysql.user set password=password('123456') where user='root' and host='localhost';
alter user user() identified by 'xxxxx'忘记密码如何找回呢？
本想的忽略授权表启动服务来找回，但是均失败，目前没找到忘记密码时找回密码的方法
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables
四、关于初始化的介绍：
4.1 初始化时要提前创建好.my.cnf文件
提示：初始化时要在/usr/local/mysql/ 目录下创建一个空的影藏文件.my.cnf文件，否则会提示文件不存在，导致初始化失败
Could not open required defaults file: /usr/local/mariadb-10.5.1-linux-glibc_214-x86_64/.my.cnf
Fatal error in defaults handling. Program aborted
Installing MariaDB/MySQL system tables in '/data/mysql/mysql3306/data' ...
Could not open required defaults file: /usr/local/mariadb-10.5.1-linux-glibc_214-x86_64/.my.cnf
Fatal error in defaults handling. Program aborted4.2初始化参数介绍
此处重点介绍初始化时的参数--auth-root-authentication-method
初始化参数--auth-root-authentication-method={normal | socket} 官方介绍：
If set to normal, it creates a root@localhost account that authenticates with the mysql_native_password authentication plugin and that has no initial password set, which can be insecure.
If set to socket, it creates a root@localhost account that authenticates with the unix_socket authentication plugin.
Set to socket by default from MariaDB 10.4 (see Authentication from MariaDB 10.4), or normal by default in earlier versions. Available since MariaDB 10.1.
官网地址：https://mariadb.com/kb/en/mysql_install_db/大意是：默认从MariaDB 10.4 之后初始化时采用的socket方式
--auth-root-authentication-method 这个参数是从MariaDB 10.1.开始引进的
初始化命令：
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --auth-root-authentication-method=normal --defaults-file=./.my.cnf --auth-root-authentication-method=normal 采用这个参数初始化，初始化完成启动服务后是允许下面命令来设置密码，而且生效
/usr/local/mysql/bin/mysqladmin -u root password '654321'
也可采用下面的方式设置密码和修改密码：
ALTER USER 'root'@'localhost' IDENTIFIED BY '654321';
采用下面方式修改MariaDB线上的账户的密码:
(root@'mgr01':mysql.sock)[(none)]>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
Query OK, 0 rows affected (0.012 sec)
但是不支持下面的命令修改密码：
update mysql.user set authentication_string=password('123456') where user='root' and host='localhost';
update mysql.user set password=password('123456') where user='root' and host='localhost';
alter user user() identified by 'xxxxx'初始化过程演示：
采用 --auth-root-authentication-method=norma进行初始化：
[root@mgr01 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --auth-root-authentication-method=normal --defaults-file=./.my.cnf
Installing MariaDB/MySQL system tables in '/data/mysql/mysql3306/data' ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
'/usr/local/mysql/bin/mysqladmin' -u root password 'new-password'
'/usr/local/mysql/bin/mysqladmin' -u root -h mgr01 password 'new-password'
Alternatively you can run:
'/usr/local/mysql/bin/mysql_secure_installation'
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/data/mysql/mysql3306/data'
You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/采用参数--auth-root-authentication-method=socket 初始化：
./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --defaults-file=./.my.cnf
[root@mgr01 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --auth-root-authentication-method=socket --defaults-file=./.my.cnf
Installing MariaDB/MySQL system tables in '/data/mysql/mysql3306/data' ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/data/mysql/mysql3306/data'
You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/提示： 此次安装的版本是10.5.1所以初始化不加参数--auth-root-authentication-method=socket 默认就是socket方式
采用这个参数初始化，初始化完成启动服务后是允许下面命令修改密码的，但是不生效
[root@mgr01 mysql]# /usr/local/mysql/bin/mysqladmin -u root password '654321'
必须采用下面的方式设置密码和修改密码：
ALTER USER 'root'@'localhost' IDENTIFIED BY '654321';
采用下面方式修改MariaDB线上的账户的密码:
(root@'mgr01':mysql.sock)[(none)]>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
Query OK, 0 rows affected (0.012 sec)
本博文二进制安装mariaDB介绍完成，下面是此博文演示环境用到的/etc/my.cnf配置文件参数内容如下：
提示：MariaDB数据库默认就开启了Gtid复制的，配置文件中是没有开启gtid这个参数的。同时本配置文件添加了复制过滤参数
（一般主要是在从库上开启复制过滤参数），mariaDB也是支持复制过滤的
[root@mgr02 support-files]# egrep -v "^#|^$" /etc/my.cnf
[client]
port = 3306
[mysql]
auto-rehash
prompt="\\u@\\h [\\d]>"
[mysqld]
user =mysql # mysql
basedir =/usr/local/mysql/ # /usr/local/mysql/
datadir =/data/mysql/mysql3306/data # /usr/local/mysql/data
server_id =1323306 # 0
port =3306 # 3306
character_set_server =utf8 # latin1
explicit_defaults_for_timestamp =off # off
socket =/tmp/mysql.sock # /tmp/mysql.sock
read_only =0 # off
skip_name_resolve =1 # 0
auto_increment_increment =1 # 1
auto_increment_offset =1 # 1
lower_case_table_names =1 # 0
secure_file_priv = # null
open_files_limit =65536 # 1024
max_connections =1000 # 151
thread_cache_size =64 # 9
table_open_cache =81920 # 2000
table_definition_cache =4096 # 1400
table_open_cache_instances =64 # 16
max_prepared_stmt_count =1048576 #
binlog_format =row # row
log_bin =/data/mysql/mysql3306/binlog/mysql-bin # off
binlog_rows_query_log_events =on # off
log_slave_updates =on # off
expire_logs_days =7 # 0
binlog_cache_size =65536 # 65536(64k)
sync_binlog =1 # 1
log_error =/data/mysql/mysql3306/logs/error.log # /usr/local/mysql/data/localhost.localdomain.err
general_log =off # off
general_log_file =/data/mysql/mysql3306/logs/general.log # hostname.log
slow_query_log =on # off
slow_query_log_file =/data/mysql/mysql3306/logs/slow.log # hostname.log
long_query_time =1.000000 # 10.000000
skip_slave_start =1 #
slave_parallel_workers =4 # 0
rpl_semi_sync_master_enabled =OFF
rpl_semi_sync_slave_enabled =ON
rpl_semi_sync_master_wait_no_slave =ON
rpl_semi_sync_master_timeout =1000000000000000000
default_storage_engine =innodb # innodb
default_tmp_storage_engine =innodb # innodb
innodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextend##线上给1024M
innodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextend
innodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_pool
innodb_log_group_home_dir =/data/mysql/mysql3306/data # ./
innodb_log_files_in_group =4 # 2##线上给4
innodb_log_file_size =100M # 50331648(48M)###线上给1024M
innodb_file_per_table =on # on
innodb_online_alter_log_max_size =128M # 134217728(128M)
innodb_open_files =65535 # 2000
innodb_page_size =16k # 16384(16k)
innodb_thread_concurrency =0 # 0
innodb_read_io_threads =4 # 4
innodb_write_io_threads =4 # 4
innodb_purge_threads =4 # 4(垃圾回收)
innodb_page_cleaners =4 # 4(刷新lru脏页)
innodb_print_all_deadlocks =on # off
innodb_deadlock_detect =on # on
innodb_lock_wait_timeout =20 # 50
innodb_spin_wait_delay =128 # 6
innodb_autoinc_lock_mode =2 # 1
innodb_io_capacity =200 # 200##sas盘iops 在145
innodb_io_capacity_max =2000 # 2000
innodb_stats_auto_recalc =on # on
innodb_stats_persistent =on # on
innodb_stats_persistent_sample_pages =20 # 20
innodb_adaptive_hash_index =on # on
innodb_change_buffering =all # all
innodb_change_buffer_max_size =25 # 25
innodb_flush_neighbors =1 # 1
innodb_doublewrite =on # on
innodb_log_buffer_size =128M # 16777216(16M)
innodb_flush_log_at_timeout =1 # 1
innodb_flush_log_at_trx_commit =1 # 1
innodb_buffer_pool_size =500M # 134217728(128M)##线上给物理内存的50%-80%
innodb_buffer_pool_instances =4
autocommit =1 # 1
innodb_old_blocks_pct =37 # 37
innodb_old_blocks_time =1000 # 1000
innodb_read_ahead_threshold =56 # 56 (0..64)
innodb_random_read_ahead =OFF # OFF
innodb_buffer_pool_dump_pct =25 # 25
innodb_buffer_pool_dump_at_shutdown =ON # ON
innodb_buffer_pool_load_at_startup =ON # ON
replicate_wild_do_table=test.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=orchestrator.%
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
performance_schema =off # on
performance_schema_consumer_global_instrumentation =on # on
performance_schema_consumer_thread_instrumentation =on # on
performance_schema_consumer_events_stages_current =on # off
performance_schema_consumer_events_stages_history =on # off
performance_schema_consumer_events_stages_history_long =off # off
performance_schema_consumer_statements_digest =on # on
performance_schema_consumer_events_statements_current =on # on
performance_schema_consumer_events_statements_history =on # on
performance_schema_consumer_events_statements_history_long =on # off
performance_schema_consumer_events_waits_current =on # off
performance_schema_consumer_events_waits_history =on # off
performance_schema_consumer_events_waits_history_long =off # off
performance-schema-instrument ='memory/%=COUNTED'
[root@mgr02 support-files]# 文章题目：mariadb-10.5.1二进制安装
文章路径：http://scyanting.com/article/jpphio.html
