本文共 20029 字，大约阅读时间需要 66 分钟。
1、安装MariaDB
[root@node1 ~]# yum -y install mariadb mariadb-server安装完成MariaDB，首先启动MariaDB[root@node1 ~]# systemctl start mariadb设置开机启动[root@node1 ~]# systemctl enable mariadbCreated symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.接下来进行MariaDB的相关简单配置[root@node1 ~]# mysql_secure_installationNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDBSERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none):
此处初次运行直接回车OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.Set root password? [Y/n] y 是否设置root用户密码，输入y并回车或直接回车
New password: 设置root用户的密码Re-enter new password: 再输入一次你设置的密码Password updated successfully!Reloading privilege tables..... Success!By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] 是否删除匿名用户，回车
... Success!Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] 是否禁止root远程登录,回车,
... Success!By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] 是否删除test数据库，回车
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.Reload privilege tables now? [Y/n] 是否重新加载权限表，回车
... Success!Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.Thanks for using MariaDB! （初始化MariaDB完成，接下来测试登录）！
下面我们正式进入 mariadb 的使用中！！[root@node1 ~]# mysql -u root -p （-u：用户名，默认为root，-p：用户密码）Enter password: （此处输入设置的密码）Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 11Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
2、配置MariaDB的字符集[root@node1 ~]# vim /etc/my.cnf
[mysqld]init_connect='SET collation_connection = utf8_unicode_ci'init_connect='SET NAMES utf8'character-set-server=utf8collation-server=utf8_unicode_ciskip-character-set-client-handshake datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock#Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0#Settings user and group are ignored when systemd is used.#If you need to run mysqld under a different user or group,#customize your systemd unit file for mariadb according to the#instructions in[mysqld_safe]
log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid#include all files from the config directory!includedir /etc/my.cnf.d
在[mysqld]标签下添加“ init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake ”[root@node1 ~]# vim /etc/my.cnf.d/client.cnf
#These two groups are read by the client library#Use it for options that affect all clients, but not the server[client]
default-character-set=utf8
#This group is not read by mysql client library,#If you use the same .cnf file for MySQL and MariaDB,#use it for MariaDB-only client options[client-mariadb]在[client]中添加： default-character-set=utf8[root@node1 ~]# vim /etc/my.cnf.d/mysql-clients.cnf
#These groups are read by MariaDB command-line tools#Use it for options that affect only one utility[mysql]default-character-set=utf8
[mysql_upgrade][mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
[mysqlimport]
[mysqlshow]
[mysqlslap]
在[mysql]中添加： default-character-set=utf83、之后进入MariaDB查看字符集
[root@node1 ~]# mysql -u root -pEnter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+| Variable_name | Value |+--------------------------+----------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | utf8 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)+----------------------+-----------------+
| Variable_name | Value |+----------------------+-----------------+| collation_connection | utf8_unicode_ci || collation_database | utf8_unicode_ci || collation_server | utf8_unicode_ci |+----------------------+-----------------+3 rows in set (0.00 sec)MariaDB [(none)]> 至此，我们的实验已经完成！注：所谓二进制包就是官方编译好了的，我们稍加配置即可使用，我们要解压缩到官方编译的路径上“ /usr/local/ ”目录下，因为安装好yum源后会有“ mysql ”账号生成，同时，也会创建一个组也是“ mysql ”；所以，在解压缩前，我们先手动创建mysql账号，
1、首先将官方安装包rz到虚拟机：[root@centos7 ~]# rz[root@centos7 ~]# ls /usr/local/bin etc games include lib lib64 libexec sbin share src[root@centos7 ~]# useradd -s /sbin/nologin -r mysql[root@centos7 ~]# getent passwd mysqlmysql:x:988:982::/home/mysql:/sbin/nologin[root@centos7 ~]# id mysqluid=988(mysql) gid=982(mysql) groups=982(mysql)[root@centos7 ~]# tar xvf mariadb-10.2.22-linux-x86_64.tar.gz -C /usr/local/[root@centos7 ~]# cd /usr/local/[root@centos7 /usr/local]# lsbin games lib libexec sbin srcetc include lib64 mariadb-10.2.22-linux-x86_64 share[root@centos7 /usr/local]# ln -s mariadb-10.2.22-linux-x86_64/ mysql [root@centos7 /usr/local]# lltotal 0drwxr-xr-x. 2 root root 6 Apr 11 2018 bindrwxr-xr-x. 2 root root 6 Apr 11 2018 etcdrwxr-xr-x. 2 root root 6 Apr 11 2018 gamesdrwxr-xr-x. 2 root root 6 Apr 11 2018 includedrwxr-xr-x. 2 root root 6 Apr 11 2018 libdrwxr-xr-x. 2 root root 6 Apr 11 2018 lib64drwxr-xr-x. 2 root root 6 Apr 11 2018 libexecdrwxrwxr-x 12 1021 1004 290 Feb 11 2019 mariadb-10.2.22-linux-x86_64lrwxrwxrwx 1 root root 29 Feb 10 22:09 mysql -> mariadb-10.2.22-linux-x86_64/drwxr-xr-x. 2 root root 6 Apr 11 2018 sbindrwxr-xr-x. 5 root root 49 Jan 3 22:01 sharedrwxr-xr-x. 2 root root 6 Apr 11 2018 src上述内容解释：用软连接把文件改名为mysql，2、[root@centos7 /usr/local]# pwd/usr/local[root@centos7 /usr/local]# ll /usr/local/mysql/total 176drwxrwxr-x 2 1021 1004 4096 Dec 20 21:15 bin-rw-r--r-- 1 1021 1004 17987 Feb 10 07:24 COPYING-rw-r--r-- 1 1021 1004 86263 Feb 10 07:24 COPYING.thirdparty-rw-r--r-- 1 1021 1004 2354 Feb 10 07:24 CREDITSdrwxrwxr-x 3 1021 1004 18 Feb 11 2019 data-rw-r--r-- 1 1021 1004 8245 Feb 10 07:24 EXCEPTIONS-CLIENTdrwxrwxr-x 3 1021 1004 19 Feb 11 2019 include-rw-r--r-- 1 1021 1004 8694 Feb 10 07:24 INSTALL-BINARYdrwxrwxr-x 5 1021 1004 335 Dec 20 21:15 libdrwxrwxr-x 4 1021 1004 30 Feb 11 2019 mandrwxrwxr-x 11 1021 1004 4096 Feb 11 2019 mysql-test-rw-r--r-- 1 1021 1004 2469 Feb 10 07:24 README.md-rw-r--r-- 1 1021 1004 19561 Feb 10 07:24 README-wsrepdrwxrwxr-x 2 1021 1004 30 Feb 11 2019 scriptsdrwxrwxr-x 32 1021 1004 4096 Feb 11 2019 sharedrwxrwxr-x 4 1021 1004 4096 Feb 11 2019 sql-benchdrwxrwxr-x 3 1021 1004 275 Feb 11 2019 support-files此时，我们用ll看到，文件都是不存在文件名和组名，所以要改，[root@centos7 /usr/local]# chown -R root.root /usr/local/mysql/[root@centos7 /usr/local]# ll /usr/local/mysql/total 176drwxrwxr-x 2 root root 4096 Dec 20 21:15 bin-rw-r--r-- 1 root root 17987 Feb 10 07:24 COPYING-rw-r--r-- 1 root root 86263 Feb 10 07:24 COPYING.thirdparty-rw-r--r-- 1 root root 2354 Feb 10 07:24 CREDITSdrwxrwxr-x 3 root root 18 Feb 11 2019 data-rw-r--r-- 1 root root 8245 Feb 10 07:24 EXCEPTIONS-CLIENTdrwxrwxr-x 3 root root 19 Feb 11 2019 include-rw-r--r-- 1 root root 8694 Feb 10 07:24 INSTALL-BINARYdrwxrwxr-x 5 root root 335 Dec 20 21:15 libdrwxrwxr-x 4 root root 30 Feb 11 2019 mandrwxrwxr-x 11 root root 4096 Feb 11 2019 mysql-test-rw-r--r-- 1 root root 2469 Feb 10 07:24 README.md-rw-r--r-- 1 root root 19561 Feb 10 07:24 README-wsrepdrwxrwxr-x 2 root root 30 Feb 11 2019 scriptsdrwxrwxr-x 32 root root 4096 Feb 11 2019 sharedrwxrwxr-x 4 root root 4096 Feb 11 2019 sql-benchdrwxrwxr-x 3 root root 275 Feb 11 2019 support-files以上内容就可以看到，文件属于root文件名root组名。root@centos7 /usr/local]# ll /usr/local/mysql/bin/total 812908-rwxr-xr-x 1 root root 12231048 Feb 10 07:30 mysql-rwxr-xr-x 1 root root 117334166 Feb 10 07:48 mysqld此文件夹下放了二进制程序，mysql是客户端工具，mysqld是服务器端程序，为了使用该文件夹的程序较为方便，我们要修改$PATH:[root@centos7 /usr/local]# vim /etc/profile.d/env.shPATH=/usr/local/mysql/bin:$PATH [root@centos7 /usr/local]# . /etc/profile.d/env.sh (使其生效）[root@centos7 /usr/local]# echo $PATH/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin[root@centos7 /usr/local]# lsbin games lib libexec mysql shareetc include lib64 mariadb-10.2.22-linux-x86_64 sbin src[root@centos7 /usr/local]# ls mysql/bin EXCEPTIONS-CLIENT mysql-test sql-benchCOPYING include README.md support-filesCOPYING.thirdparty INSTALL-BINARY README-wsrepCREDITS lib scriptsdata man share此时，变量问题，我们已经解决。3、创建存放数据库的文件：[root@centos7 /usr/local]# mkdir /data/mysql -pvmkdir: created directory ‘/data/mysql’[root@centos7 /usr/local]# ll -d /data/mysql/drwxr-xr-x 2 root root 6 Feb 10 22:29 /data/mysql/[root@centos7 /usr/local]# chown mysql.mysql /data/mysql/ [root@centos7 /usr/local]# ls /data/mysql/[root@centos7 /usr/local]# 我们创建的文件夹有问题，要更改所属组，但是由上可以看到，文件夹里无内容。[root@centos7 /usr/local]# cd mysql/[root@centos7 /usr/local/mysql]# lsbin EXCEPTIONS-CLIENT mysql-test sql-benchCOPYING include README.md support-filesCOPYING.thirdparty INSTALL-BINARY README-wsrepCREDITS lib scriptsdata man share[root@centos7 /usr/local/mysql]# cd scripts/[root@centos7 /usr/local/mysql/scripts]# lsmysql_install_db其中该脚本mysql_install_db就是用来生成系统数据库的，[root@centos7 /usr/local/mysql/scripts]# pwd/usr/local/mysql/scripts[root@centos7 /usr/local/mysql/scripts]# cd .[root@centos7 /usr/local/mysql/scripts]# cd ..[root@centos7 /usr/local/mysql]# ./scripts/mysql_install_db --datadir=/data/mysql --user=mysqlInstalling MariaDB/MySQL system tables in '/data/mysql' ...OKTo start mysqld at boot time you have to copy
support-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:'./bin/mysqladmin' -u root password 'new-password'
'./bin/mysqladmin' -u root -h centos7 password 'new-password'Alternatively you can run:
'./bin/mysql_secure_installation'which will also give you the option of removing the test
databases and anonymous user created by default. This isstrongly recommended for production servers.See the MariaDB Knowledgebase at or the
MySQL manual for more instructions.You can start the MariaDB daemon with:
cd '.' ; ./bin/mysqld_safe --datadir='/data/mysql'You can test the MariaDB daemon with mysql-test-run.pl
cd './mysql-test' ; perl mysql-test-run.plPlease report any problems at
The latest information about MariaDB is available at .
You can find additional information about the MySQL part at:Consider joining MariaDB's strong and vibrant community:此时已经安装完成，我们必须在/usr/local/mysql/script下才能运行脚本，否则，在子目录下不能运行。4、[root@centos7 /usr/local/mysql]# ls /data/mysql/ -l
total 110620-rw-rw---- 1 mysql mysql 16384 Feb 10 22:38 aria_log.00000001-rw-rw---- 1 mysql mysql 52 Feb 10 22:38 aria_log_control-rw-rw---- 1 mysql mysql 938 Feb 10 22:38 ib_buffer_pool-rw-rw---- 1 mysql mysql 12582912 Feb 10 22:38 ibdata1-rw-rw---- 1 mysql mysql 50331648 Feb 10 22:38 ib_logfile0-rw-rw---- 1 mysql mysql 50331648 Feb 10 22:38 ib_logfile1drwx------ 2 mysql root 4096 Feb 10 22:38 mysqldrwx------ 2 mysql mysql 20 Feb 10 22:38 performance_schemadrwx------ 2 mysql root 6 Feb 10 22:38 test此时已经生成相应的数据库了，如上。[root@centos7 /usr/local/mysql]# lsbin include README-wsrepCOPYING INSTALL-BINARY scriptsCOPYING.thirdparty lib shareCREDITS man sql-benchdata mysql-test support-filesEXCEPTIONS-CLIENT README.md[root@centos7 /usr/local/mysql]# cd support-files/[root@centos7 /usr/local/mysql/support-files]# lsbinary-configure my-medium.cnf policymagic my-small.cnf wsrep.cnfmy-huge.cnf mysqld_multi.server wsrep_notifymy-innodb-heavy-4G.cnf mysql-log-rotatemy-large.cnf mysql.server上述文件中就已可以用作mysql配置文件的模板范例配置文件。5、[root@centos7 /usr/local/mysql/support-files]# mkdir /etc/mysql[root@centos7 /usr/local/mysql/support-files]# cp my-huge.cnf /etc/mysql/my.cnf[root@centos7 /usr/local/mysql/support-files]# vim /etc/mysql/my.cnf[mysqld]datadir=/data/mysqlport = 3306socket = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 1Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 32M我们创建一个文件并cp文件，编辑进入“ ”datadir=/data/mysql ”内容。我们欠缺一个启动脚本，我们拷贝一份并改名，【】我们把它启动起来，如下：6、[root@centos7 /usr/local/mysql/support-files]# lsbinary-configure my-medium.cnf policymagic my-small.cnf wsrep.cnfmy-huge.cnf mysqld_multi.server wsrep_notifymy-innodb-heavy-4G.cnf mysql-log-rotatemy-large.cnf mysql.server[root@centos7 /usr/local/mysql/support-files]# cp mysql.server /etc/init.d/mysqld[root@centos7 /usr/local/mysql/support-files]# cd /etc/init.d[root@centos7 /etc/init.d]# lsfunctions mysqld netconsole network README[root@centos7 /etc/init.d]# service mysqld statusERROR! MariaDB is not running现在，我们启动服务，并查看端口：[root@centos7 /etc/init.d]# service mysqld startReloading systemd: [ OK ]Starting mysqld (via systemctl): [ OK ][root@centos7 /etc/init.d]# ss -ntlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 :111 : LISTEN 0 128 :6000 : LISTEN 0 5 192.168.122.1:53 : LISTEN 0 128 :22 : LISTEN 0 128 127.0.0.1:631 : LISTEN 0 100 127.0.0.1:25 : LISTEN 0 128 127.0.0.1:6010 : LISTEN 0 128 127.0.0.1:6011 : LISTEN 0 80 :::3306 ::: LISTEN 0 128 :::111 ::: LISTEN 0 128 :::80 ::: LISTEN 0 128 :::6000 ::: LISTEN 0 128 :::22 ::: LISTEN 0 128 ::1:631 ::: LISTEN 0 100 ::1:25 ::: LISTEN 0 128 ::1:6010 ::: LISTEN 0 128 ::1:6011 ::: [root@centos7 /etc/init.d]# ls /data/mysql/aria_log.00000001 ibdata1 multi-master.info performance_schemaaria_log_control ib_logfile0 mysql testcentos7.pid ib_logfile1 mysql-bin.000001ib_buffer_pool ibtmp1 mysql-bin.index[root@centos7 /etc/init.d]# ll /data/mysql/total 122920-rw-rw---- 1 mysql mysql 16384 Feb 10 22:38 aria_log.00000001-rw-rw---- 1 mysql mysql 52 Feb 10 22:38 aria_log_control-rw-rw---- 1 mysql mysql 6 Feb 10 22:55 centos7.pid-rw-rw---- 1 mysql mysql 938 Feb 10 22:38 ib_buffer_pool-rw-rw---- 1 mysql mysql 12582912 Feb 10 22:55 ibdata1-rw-rw---- 1 mysql mysql 50331648 Feb 10 22:55 ib_logfile0-rw-rw---- 1 mysql mysql 50331648 Feb 10 22:38 ib_logfile1-rw-rw---- 1 mysql mysql 12582912 Feb 10 22:55 ibtmp1-rw-rw---- 1 mysql mysql 0 Feb 10 22:55 multi-master.infodrwx------ 2 mysql root 4096 Feb 10 22:38 mysql-rw-rw---- 1 mysql mysql 328 Feb 10 22:55 mysql-bin.000001-rw-rw---- 1 mysql mysql 19 Feb 10 22:55 mysql-bin.indexdrwx------ 2 mysql mysql 20 Feb 10 22:38 performance_schemadrwx------ 2 mysql root 6 Feb 10 22:38 test文件已经生成，我们的MySQL服务就可以连接了，10.2.22版本，如下：[root@centos7 /etc/init.d]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 10Server version: 10.2.22-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
我们为了安全，要跑一个安全加固脚本，7、root@centos7 /etc/init.d]# which mysql_secure_installation /usr/local/mysql/bin/mysql_secure_installation[root@centos7 /etc/init.d]# mysql_secure_installationNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none):
OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.Set root password? [Y/n] y
New password: Re-enter new password: Password updated successfully!Reloading privilege tables..... Success!By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y 删除匿名用户
... Success!Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] y 禁止远程登录
... Success!By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] y 删除text账号
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.Reload privilege tables now? [Y/n] y 使其生效
... Success!Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.Thanks for using MariaDB!
[root@centos7 /etc/init.d]# mysqlERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)可以看到匿名用户就登陆不了了，[root@centos7 /etc/init.d]# mysql -uroot -p12345gxyWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 20Server version: 10.2.22-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
只有输入正确的密码用户才能登陆，到此，二进制编译安装已经完成。步骤总结：1 准备用户和组useradd -s /sbin/nologin -r mysql 2 准备二进制程序文件tar xvf mariadb-10.2.22-linux-x86_64.tar.gz -C /usr/local/cd /usr/local/ln -s mariadb-10.2.22-linux-x86_64/ mysqlchown -R root.root /usr/local/mysql/3 PATH变量cat /etc/profile.d/env.sh PATH=/usr/local/mysql/bin:$PATH4 准备数据库数据目录和数据 （4和2是两码事，不要混淆）mkdir /data/mysql -pvchown mysql.mysql /data/mysql/cd /usr/local/mysql./scripts/mysql_install_db --datadir=/data/mysql --user=mysql5 准备Mysql的服务器端的配置文件mkdir /etc/mysqlcp /usr/local/mysql/support-files/my-huge.cnf /etc/mysql/my.cnfvim /etc/mysql/my.cnf
[mysqld]datadir=/data/mysql 加一行6 准备服务启动脚本cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqldservice mysqld start7 安全加固mysql_secure_installation转载于:https://blog.51cto.com/14128387/2349072