CentOS用yum安装、配置MariaDB

CentOS用yum安装、配置MariaDB

设置yum源

新建/etc/yum.repos.d/MariaDB.repo
baseurl可以自由跟换

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

也可以阿里的源

[mariadb]
name = MariaDB
baseurl = http://mirrors.aliyun.com/mariadb/yum/10.2/centos7-amd64
gpgkey = http://mirrors.aliyun.com/mariadb/yum//RPM-GPG-KEY-MariaDB
gpgcheck = 1

查看yum源

yum list | grep mariadb

yum安装

yum install MariaDB-server MariaDB-client

如果你需要改变默认mysql的存储路径可以执行以下

rm -rf /var/lib/mysql
mkdir /var/data/db/mariadb

配置mysql-clients.cnf

其中client一定要配置对,其他的可以参考

[client]
port        = 3306
# 我这里是自定义了mysql的存储目录,如果你用的是默认的,那么后面路径为 /var/lib/mysql/mysql.sock
socket      = /var/data/db/mariadb/mysql.sock

[mysql]
no-auto-rehash

[mysqldump]
quick
max_allowed_packet = 64M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

修改配置文件/etc/my.cnf.d/server.cnf

[mysqld]
port            = 3306
# 如果你这里是默认的datadir就写 /var/lib/mysql
datadir         = /var/data/db/mariadb
socket      = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 64M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1

# 开启慢查询
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

初始化数据

# 使用自定义目录初始化
mysql_install_db --defaults-file=/etc/my.cnf --datadir=/var/data/db/mariadb/ --user=mysql
# 使用默认的路径初始化
mysql_install_db --defaults-file=/etc/my.cnf --user=mysql

启动服务

service mysql start
或者
systemctl start mariadb

设置开机启动

systemctl enable mariadb

设置ROOT账户密码

mysqladmin -u root password "xxxx"

或者执行mysql_secure_installation

首先是设置密码,会提示先输入密码

Enter current password for root (enter for none):<–初次运行直接回车

设置密码

Set root password? [Y/n] <– 是否设置root用户密码,输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码

其他配置

Remove anonymous users? [Y/n] <– 是否删除匿名用户,回车

Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,

Remove test database and access to it? [Y/n] <– 是否删除test数据库,回车

Reload privilege tables now? [Y/n] <– 是否重新加载权限表,回车

初始化MariaDB完成,接下来测试登录

mysql -uroot -p [回车,之后输入密码]

测试

mysql -uroot -pxxxx

设置远程登录

此设置方式同样适用于mysql

# 不限制IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxx' WITH GRANT OPTION;
# 指定IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.1' IDENTIFIED BY 'xxxx' WITH GRANT OPTION;

# 刷新权限
FLUSH PRIVILEGES;

配置MariaDB的字符集

查看字符集
show variables like "%character%";show variables like "%collation%";
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| 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)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
设置客户端

vim /etc/my.cnf.d/mysql-clients.cnf

[mysql]
default-character-set=utf8mb4
设置服务端

vim /etc/my.cnf.d/server.cnf

[mysqld]
#init_connect='SET collation_connection = utf8_general_ci'
#init_connect='SET NAMES utf8'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
#skip-character-set-client-handshake

重启数据库检查字符集

systemctl restart mariadb
show variables like "%character%";show variables like "%collation%";

关于密码强度

错误

Bad magic header in tc log

2018-10-09  9:51:23 0 [ERROR] Bad magic header in tc log
2018-10-09  9:51:23 0 [ERROR] Crash recovery failed. Either correct the problem (if it's, for example, out of memory error) and restart, or delete tc log and start mysqld with --tc-heuristic-recover={commit|rollback}
2018-10-09  9:51:23 0 [ERROR] Can't init tc log
2018-10-09  9:51:23 0 [ERROR] Aborting

删除 mysql 目录下的 tg.log

Ubuntu下启动 停止 重启mysql

安装mariadb

sudo apt install mariadb-server
# 启动
sudo systemctl status|start mysql.service

第一次设置权限

sudo mysql_secure_installation
- Enter current password for root (enter for none): (直接回车就好,因为这是初始化密码,自然没有密码)
- Set root password? [Y/n] y (设置 root 密码)
- Remove anonymous users? [Y/n] y (移除匿名账户)
- Disallow root login remotely? [Y/n] y (禁用 root 远程登录)
- Remove test database and access to it? [Y/n] y (删除 test 数据库和访问权限)
- Reload privilege tables now? [Y/n] y (刷新权限)

本地登录

sudo mysql

卸载mariadb

sudo apt-get purge mariadb-*