mariadb(mysql) 安装与使用,备份及恢复

为什么选择mariadb

前提是开发环境。mariadb体积小,安装方便,兼容常用mysql指令

安装

#Server version: 5.5.68-MariaDB MariaDB Server
yum install mariadb-server

开机自启

systemctl enable mariadb 

常用命令

systemctl start mariadb  ️启动MariaDB
systemctl stop mariadb  ️停止MariaDB
systemctl restart mariadb  ️重启MariaDB

开发环境设置root账号以任意ip,空密码登陆

#将Host设置为%,表示任意ip
update user set Host='%' where Host='localhost';
#如下,保留一条即可
MariaDB [mysql]> select Host, User, Password from user;
+------+------+----------+
| Host | User | Password |
+------+------+----------+
| %    | root |          |
+------+------+----------+
1 row in set (0.00 sec)

#重载权限
FLUSH PRIVILEGES;

备份

下面提供一个备份所有数据库的脚本

dir='/vagrant/apps/mysql/backup/'`date +%Y%m%d`;
filename='all_mysql'`date +%Y%m%d%H%M`'.sql.gz';
mkdir $dir
cd $dir
# 把所有数据库名导出到 databases.txt,排除掉mysql, information_schema, performance_schema
mysql -e "show databases;" -uroot | grep -Ev "Database|mysql|information_schema|performance_schema" > databases.txt
# 把所有数据库导出到一个.sql.gz文件,排除掉mysql, information_schema, performance_schema
mysql -e "show databases;" -uroot | grep -Ev "Database|mysql|information_schema|performance_schema" | xargs mysqldump --skip-lock-tables -uroot --databases | gzip> $filename

恢复

# 解压
gzip -d all_mysql201912020333.sql.gz
# 导入
mysql -uroot < all_mysql201912020333.sql.gz

感谢阅读这篇文章,如果你喜欢,或者遇到了问题,可以关注我的公众号