Ubuntu 18.04 mariadb 替换mysql

数据备份

在卸载mysql前，进行数据库备份

mysqldump -uroot -p${password} --databases ${your database}>/opt/test.sql

卸载mysql

 1、 首先在系统终端中查看MySQL的依赖项，运行命令：

dpkg --list|grep mysql

 

2、卸载依赖项

apt-get remove mysql-common
apt-get autoremove --purge mysql-server-5.7
#清除残留数据
dpkg -l|grep ^rc|awk '{print$2}'|xargs dpkg -P
apt-get autoremove --purge mysql-server-core-5.7
apt-get autoremove --purge mysql-client-core-5.7
dpkg -l|grep ^rc|awk '{print$2}'|xargs dpkg -P

至此已经没有了MySQL的依赖项，彻底删除MySQL5.7版本了

安装mariadb

1、解压

tar -zxvf mariadb-10.5.5-linux-x86_64.tar.gz
mv  mariadb-10.5.5-linux-x86_64  /usr/local/mariadb
ln -s  /usr/local/mariadb  /usr/local/mysql

2、创建数据目录

cd /usr/local/mysql

useradd mysql -s /sbin/nologin -M
mkdir /usr/local/mysql/{data,binlog,logs,tmp}  -p

chown -R mysql .
chgrp -R mysql .
chown -R mysql.mysql -R data

3、初始化

cd /usr/local/mysql
touch .my.cnf
./scripts/mysql_install_db  --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --defaults-file=./.my.cnf

4、初始化设置密码

cd /usr/local/mysql/bin
./mysql_secure_installation

5、安装mariadb-client

dpkg -i mysql-common_5.8+1.0.4_all.deb
dpkg -i libaio1_0.3.110-5ubuntu0.1_amd64.deb
dpkg -i mariadb-common_1%3a10.1.44-0ubuntu0.18.04.1_all.deb
dpkg -i mariadb-client-core-10.1_1%3a10.1.44-0ubuntu0.18.04.1_amd64.deb

6、配置/etc/my.cnf

[client]
port            = 3306

[mysql]
prompt = "MariaDB:\\u@\\h > "
default-character-set = 'utf8'
auto-rehash = FALSE
local-infile = 1
max-allowed-packet = 64M
secure-auth = TRUE

[mysqld]
user = mysql
server-id       = 1323306
port            = 3306
socket          = /tmp/mysql.sock
basedir         = /usr/local/mysql
datadir         = /usr/local/mysql/data
log-error       = /usr/local/mysql/logs/error.log
tmpdir          = /usr/local/mysql/tmp

log-bin = /usr/local/mysql/binlog/mariadb-bin
binlog-format = ROW
expire-logs-days = 15
max-binlog-size = 1024M
sync-binlog = 1
log-slave-updates

skip-name-resolve
lower_case_table_names = 1
event_scheduler = 1
character-set-server = 'utf8'
collation-server = 'utf8_general_ci'
default-storage-engine = 'InnoDB'
transaction-isolation = READ-COMMITTED
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 32M

innodb-flush-log-at-trx-commit = 1
innodb-buffer-pool-size = 2G
innodb-buffer-pool-instances = 2
innodb-file-per-table = 1

general_log_file = general.log
log-warnings = 2
log_error = error.log
slow-query-log = 1
slow_query_log_file = slow.log
long-query-time = 1

7、启动mariadb

启动方式一： /usr/local/mysql/bin/mysqld_safe  --defaults-file=/etc/my.cnf &
启动方式二： service mysql start

8、配置mariadb开机自启动

systemctl enable mysql

9、访问mariadb

mysql -h 127.0.0.1 -uroot -p

10、创建用户

create user ${username} identified by 'yourpassword';
grant all privileges on ${database}.* to '${username}'@'%';
flush privileges;

11、配置远程访问

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

还原数据备份

mysql -h 127.0.0.1 -uroot -p${yourpassword} <test.sql