简介：
在CentOS上安装Mariadb安装环境及版本：
系统环境：CentOS Linux release 7.5.1804 (Core)
Mariadb版本：使用yum安装一、安装
[root@localhost ~]# yum -y install mariadb mariadb-server
[root@localhost ~]# yum -y install mariadb mariadb-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.tuna.tsinghua.edu.cn
* extras: mirrors.tuna.tsinghua.edu.cn
* updates: mirrors.bfsu.edu.cn
...... 11/11
Installed:
mariadb.x86_64 1:5.5.65-1.el7 mariadb-server.x86_64 1:5.5.65-1.el7
Dependency Installed:
perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-MySQL.x86_64 0:4.023-6.el7 perl-DBI.x86_64 0:1.627-4.el7 perl-IO-Compress.noarch 0:2.061-2.el7 perl-Net-Daemon.noarch 0:0.48-5.el7
perl-PlRPC.noarch 0:0.2020-14.el7
Dependency Updated:
mariadb-libs.x86_64 1:5.5.65-1.el7
Complete!
[root@localhost ~]# 二、启停
2.1 启停
启动：[root@localhost ~]# systemctl start mariadb
停止：[root@localhost ~]# systemctl stop mariadb
重启：[root@localhost ~]# systemctl restart mariadb
2.2 自启动
命令：systemctl enable mariadb
[root@localhost ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.2.3 查看状态
命令：systemctl status mariadb
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2020-06-30 03:37:59 EDT; 1min 53s ago
Main PID: 2018 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─2018 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─2179 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
Jun 30 03:37:57 localhost.localdomain systemd[1]: Starting MariaDB database server...
Jun 30 03:37:57 localhost.localdomain mariadb-prepare-db-dir[1981]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Jun 30 03:37:57 localhost.localdomain mariadb-prepare-db-dir[1981]: If this is not the case, make sure the /var/lib/mysql is empty before running mariadb-prepare-db-dir.
Jun 30 03:37:57 localhost.localdomain mysqld_safe[2018]: 200630 03:37:57 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Jun 30 03:37:57 localhost.localdomain mysqld_safe[2018]: 200630 03:37:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Jun 30 03:37:59 localhost.localdomain systemd[1]: Started MariaDB database server.三、设置root密码及安全配置
命令：mysql_secure_installation
[root@localhost ~]# mysql_secure_installation
NOTE: 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, and
you 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 for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production 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 removed
before moving into a production environment.
Remove test database and access to it? [Y/n] N^H
Remove test database and access to it? [Y/n] n
... skipping.
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@localhost ~]# 包括设置root密码、移除匿名用户、禁止root远程登录、不用移除测试数据库、重新刷新表等
四、设置远程连接
root可远程连接命令：grant all privileges on . to ‘root’@’%’ identified by ‘pyrk-0811’ with grant option;
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (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)]> grant all privileges on *.* to 'root'@'%' identified by 'pyrk-0811' with grant option;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye启动Mariadb客户端：Navicat for MySQL
五、创建用户
创建用户：CREATE USER ‘chpdirector’@’%’ IDENTIFIED BY ‘chpdirector84’;
设置用户chpdirector远程连接：GRANT ALL PRIVILEGES ON *.* TO ‘chpdirector’@’%’ IDENTIFIED BY ‘chpdirector84’ WITH GRANT OPTION;
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (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)]> CREATE USER 'chpdirector'@'%' IDENTIFIED BY 'chpdirector84';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'chpdirector'@'%' IDENTIFIED BY 'chpdirector84' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit;
Bye
[root@localhost ~]# 用新建用户远程登录：
六、设置编码格式
6.1 查看现有编码格式
查看现有编码格式：show variables like ‘character%’;
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (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%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye6.2 编辑配置文件：vi /etc/my.cnf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-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 http://fedoraproject.org/wiki/Systemd
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
max_heap_table_size = 128M
tmp_table_size = 128M
max_allowed_packet=16M
max_connections=1024
thread_cache_size=256
sort_buffer_size=8M
bulk_insert_buffer_size = 16M
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 8M
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d6.3 重启后再次查看编码格式
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (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%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye七、设置最大连接数
centos7使用mariadb数据库时，有个默认最大连接数。
命令：show variables like ‘max_connections’;
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (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 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye仅仅修改配置文件/etc/my.cnf中的max_connections=1024是不起作用的，还需要修改vi /usr/lib/systemd/system/mariadb.service，取消[Service]前面的#号，并添加：
[Service]
LimitNOFILE=10000
LimitNPROC=10000重新加载系统服务，并重启mariadb
[root@localhost ~]# systemctl –system daemon-reload
[root@localhost ~]# systemctl restart mariadb
再次查看最大连接数：show variables like ‘max_connections’;
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (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 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1024 |
+-----------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye