mariadb-10.5.1二進(jìn)制安裝

一、本博文內(nèi)容簡介：

有關(guān)mariaDB的介紹，大家可以移步到百度百科或者mariaDB官網(wǎng)進(jìn)行查看。
本文主要是介紹mariaDB二進(jìn)制包如何進(jìn)行安裝，以及mariaDB服務(wù)安裝后的如何啟動以及賬戶安全設(shè)置等等
mariaDB截止2020-02-24日，目前發(fā)部的最新的二進(jìn)制版本是mariadb-10.5.1-linux-glibc_214-x86_64.tar.gz。本文采用這個版本的二進(jìn)制安裝。

二、二進(jìn)制安裝mariadb：

官方二進(jìn)制安裝文檔
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

創(chuàng)建數(shù)據(jù)目錄：

useradd mysql -s /sbin/nologin -M
mkdir /data/mysql/mysql3306/{data,binlog,logs,tmp} -p
cd /data/mysql/;chown -R mysql.mysql  mysql3306

安裝相關(guān)依賴和下載解壓：

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.cnf

mariaDB啟動：

啟動方式一： /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配置文件參數(shù)會附在博文最后)

mariadb-10.5.1在初始化完成后啟動報錯，發(fā)現(xiàn)my.cnf配置文件中以下參數(shù)是不支持的：

[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'

三、賬戶密碼設(shè)置、修改和找回：

安裝完成默認(rèn)是沒密碼的

采用下面設(shè)置密碼：

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'

忘記密碼如何找回呢？
本想的忽略授權(quán)表啟動服務(wù)來找回，但是均失敗，目前沒找到忘記密碼時找回密碼的方法
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables

四、關(guān)于初始化的介紹：

4.1 初始化時要提前創(chuàng)建好.my.cnf文件

提示：初始化時要在/usr/local/mysql/ 目錄下創(chuàng)建一個空的影藏文件.my.cnf文件，否則會提示文件不存在，導(dǎo)致初始化失敗

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 aborted

4.2初始化參數(shù)介紹

此處重點(diǎn)介紹初始化時的參數(shù)--auth-root-authentication-method

初始化參數(shù)--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.

官網(wǎng)地址：https://mariadb.com/kb/en/mysql_install_db/

大意是：默認(rèn)從MariaDB 10.4 之后初始化時采用的socket方式
--auth-root-authentication-method 這個參數(shù)是從MariaDB 10.1.開始引進(jìn)的

初始化命令：

/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 采用這個參數(shù)初始化，初始化完成啟動服務(wù)后是允許下面命令來設(shè)置密碼，而且生效

/usr/local/mysql/bin/mysqladmin -u root password '654321'

也可采用下面的方式設(shè)置密碼和修改密碼：
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進(jìn)行初始化：

[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/

采用參數(shù)--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所以初始化不加參數(shù)--auth-root-authentication-method=socket 默認(rèn)就是socket方式

采用這個參數(shù)初始化，初始化完成啟動服務(wù)后是允許下面命令修改密碼的，但是不生效
[root@mgr01 mysql]# /usr/local/mysql/bin/mysqladmin -u root password '654321'

必須采用下面的方式設(shè)置密碼和修改密碼：
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)

本博文二進(jìn)制安裝mariaDB介紹完成，下面是此博文演示環(huán)境用到的/etc/my.cnf配置文件參數(shù)內(nèi)容如下：
提示：MariaDB數(shù)據(jù)庫默認(rèn)就開啟了Gtid復(fù)制的，配置文件中是沒有開啟gtid這個參數(shù)的。同時本配置文件添加了復(fù)制過濾參數(shù)
（一般主要是在從庫上開啟復(fù)制過濾參數(shù)），mariaDB也是支持復(fù)制過濾的

[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)##線上給物理內(nèi)存的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]#