二进制安装mariadb的步骤

二进制安装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 -Mmkdir /data/mysql/mysql3306/{data,binlog,logs,tmp} -pcd /data/mysql/;chown -R mysql.mysql  mysql3306

安装相关依赖和下载解压：

yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 libedit -ycd /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.gztar 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配置文件参数会附在博文最后)

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

关于初始化的介绍：

初始化时要提前创建好.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.cnfFatal error in defaults handling. Program abortedInstalling 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.cnfFatal error in defaults handling. Program aborted

初始化参数介绍

此处重点介绍初始化时的参数--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' ...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE 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 testdatabases and anonymous user created by default.  This isstrongly recommended for production servers.See the MariaDB Knowledgebase at http://mariadb.com/kb or theMySQL 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.plcd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.plPlease report any problems at http://mariadb.org/jiraThe latest information about MariaDB is available at http://mariadb.org/.You can find additional information about the MySQL part at:http://dev.mysql.comConsider 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' ...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemTwo all-privilege accounts were created.One is root@localhost, it has no password, but you need tobe system 'root' user to connect. Use, for example, sudo mysqlThe second is mysql@localhost, it has no password either, butyou need to be the system 'mysql' user to connect.After connecting you can set the password, if you would need to beable to connect as any of these users with a password and without sudoSee the MariaDB Knowledgebase at http://mariadb.com/kb or theMySQL 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.plcd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.plPlease report any problems at http://mariadb.org/jiraThe latest information about MariaDB is available at http://mariadb.org/.You can find additional information about the MySQL part at:http://dev.mysql.comConsider 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-rehashprompt="\\u@\\h [\\d]>"[mysqld]user                                =mysql                          #   mysqlbasedir                             =/usr/local/mysql/              #   /usr/local/mysql/datadir                             =/data/mysql/mysql3306/data     #   /usr/local/mysql/dataserver_id                           =1323306                        #   0port                                =3306                           #   3306character_set_server                =utf8                           #   latin1explicit_defaults_for_timestamp     =off                            #    offsocket                              =/tmp/mysql.sock                #   /tmp/mysql.sockread_only                           =0                              #   offskip_name_resolve                   =1                              #   0auto_increment_increment            =1                              #   1auto_increment_offset               =1                              #   1lower_case_table_names              =1                              #   0secure_file_priv                    =                               #   nullopen_files_limit                    =65536                          #   1024max_connections                     =1000                           #   151thread_cache_size                   =64                             #   9table_open_cache                    =81920                          #   2000table_definition_cache              =4096                           #   1400table_open_cache_instances          =64                             #   16max_prepared_stmt_count             =1048576                        #binlog_format                       =row                          # rowlog_bin                             =/data/mysql/mysql3306/binlog/mysql-bin                      #  offbinlog_rows_query_log_events        =on                             #   offlog_slave_updates                   =on                             #   offexpire_logs_days                    =7                              #   0binlog_cache_size                   =65536                          #   65536(64k)sync_binlog                         =1                              #   1log_error                           =/data/mysql/mysql3306/logs/error.log                        #  /usr/local/mysql/data/localhost.localdomain.errgeneral_log                         =off                            #   offgeneral_log_file                    =/data/mysql/mysql3306/logs/general.log                    #   hostname.logslow_query_log                      =on                             #    offslow_query_log_file                 =/data/mysql/mysql3306/logs/slow.log                       #    hostname.loglong_query_time                     =1.000000                       #    10.000000skip_slave_start                     =1                              #slave_parallel_workers               =4                             #    0rpl_semi_sync_master_enabled        =OFFrpl_semi_sync_slave_enabled         =ONrpl_semi_sync_master_wait_no_slave  =ONrpl_semi_sync_master_timeout        =1000000000000000000default_storage_engine                          =innodb                     #   innodbdefault_tmp_storage_engine                      =innodb                     #   innodbinnodb_data_file_path                           =ibdata1:12M:autoextend    #    ibdata1:12M:autoextend##线上给1024Minnodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #   ibtmp1:12M:autoextendinnodb_buffer_pool_filename                     =ib_buffer_pool             #   ib_buffer_poolinnodb_log_group_home_dir                       =/data/mysql/mysql3306/data                         #   ./innodb_log_files_in_group                       =4                          #   2##线上给4innodb_log_file_size                            =100M                       #   50331648(48M)###线上给1024Minnodb_file_per_table                           =on                         #   oninnodb_online_alter_log_max_size                =128M                       #   134217728(128M)innodb_open_files                               =65535                      #   2000innodb_page_size                                =16k                        #   16384(16k)innodb_thread_concurrency                       =0                          #   0innodb_read_io_threads                          =4                          #   4innodb_write_io_threads                         =4                          #   4innodb_purge_threads                            =4                          #   4(垃圾回收)innodb_page_cleaners                            =4                          #   4(刷新lru脏页)innodb_print_all_deadlocks                      =on                         #   offinnodb_deadlock_detect                          =on                         #   oninnodb_lock_wait_timeout                        =20                         #   50innodb_spin_wait_delay                          =128                          # 6innodb_autoinc_lock_mode                        =2                          #   1innodb_io_capacity                              =200                        #   200##sas盘iops 在145innodb_io_capacity_max                          =2000                       #   2000innodb_stats_auto_recalc                        =on                         #   oninnodb_stats_persistent                         =on                         #   oninnodb_stats_persistent_sample_pages            =20                         #   20innodb_adaptive_hash_index                      =on                         #   oninnodb_change_buffering                         =all                        #   allinnodb_change_buffer_max_size                   =25                         #   25innodb_flush_neighbors                          =1                          #   1innodb_doublewrite                              =on                         #   oninnodb_log_buffer_size                          =128M                        #  16777216(16M)innodb_flush_log_at_timeout                     =1                          #   1innodb_flush_log_at_trx_commit                  =1                          #   1innodb_buffer_pool_size                         =500M                  #    134217728(128M)##线上给物理内存的50%-80%innodb_buffer_pool_instances                    =4autocommit                                      =1                          #   1innodb_old_blocks_pct                           =37                         #    37innodb_old_blocks_time                          =1000                       #    1000innodb_read_ahead_threshold                     =56                         #    56 (0..64)innodb_random_read_ahead                        =OFF                        #    OFFinnodb_buffer_pool_dump_pct                     =25                         #    25innodb_buffer_pool_dump_at_shutdown             =ON                         #    ONinnodb_buffer_pool_load_at_startup              =ON                         #    ONreplicate_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   #    onperformance_schema_consumer_global_instrumentation                      =on    #    onperformance_schema_consumer_thread_instrumentation                      =on    #    onperformance_schema_consumer_events_stages_current                       =on    #    offperformance_schema_consumer_events_stages_history                       =on    #    offperformance_schema_consumer_events_stages_history_long                  =off   #    offperformance_schema_consumer_statements_digest                           =on    #    onperformance_schema_consumer_events_statements_current                   =on    #    onperformance_schema_consumer_events_statements_history                   =on    #    onperformance_schema_consumer_events_statements_history_long              =on    #    offperformance_schema_consumer_events_waits_current                        =on    #    offperformance_schema_consumer_events_waits_history                        =on    #    offperformance_schema_consumer_events_waits_history_long                   =off   #    offperformance-schema-instrument                                           ='memory/%=COUNTED'[root@mgr02 support-files]#