一、Mysql背景信息
Mysql在互联网早期就流行了,追求速度、简单、坚持开源、几乎支持所有操作系统。完全支持多用户、多线程,支持海量数据存储,采用MyISAM、InnoDB两大存储引擎优势互补。但随着InnoDB和Mysql自身被Oracle收购,促使Mysql的创始人开发了MariaDB。
MariaDB最大的特点就是采用XtraDB存储引擎,并和原生版本兼容,保留了Mysql的自由开放,XtraDB是Percona开发的高性能存储引擎,主要是为了替代InnoDB.除了XtraDB引擎,后起之秀TokuDB支持高性能、事务处理的存储引擎,性能比InnoDB高出很多,超高的insert性能、高压缩比、支持在线修改索引、添加字段。
安装时Mysql、MariaDB、Percona Server 不能共存,要部署MariaDB就必须卸载另外两个。否则出现“mysql service masked”错误。
二、安装MariaDB
1.安装数据库环境
- dream361@ubuntu16:~$ sudo aptitude install -y mariadb-server mariadb-client
2.安装数据库实例
- dream361@ubuntu16:~$ sudo 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): 设置root的密码1
- OK, successfully used password, moving on...
- Setting the root password ensures that nobody can log into the MariaDB
- root user without the proper authorisation.
- You already have a root password set, so you can safely answer 'n'.
- Change the root password? [Y/n] n 是否改变密码:n不改变
- ... skipping.
- 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 是否移除测试用户: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 是否允许root远程登录:n不允许
- ... 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] y 删掉测试数据库test:y删掉
- - Dropping test database...
- ... Success!
- - Removing privileges on test database...
- ... Success!
- Reloading the privilege tables will ensure that all changes made so far
- will take effect immediately.
- Reload privilege tables now? [Y/n] y 重新加载权限表: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
3.查看MariaDB数据库服务状态 (这里也说明了MariaDB与其他Mysql数据库不能共存在同一操作系统)
- dream361@ubuntu16:~$ sudo systemctl status mysql
4.启动MariaDB数据库服务
- dream361@ubuntu16:~$ sudo systemctl start mysql
3.设置mysql随系统服务启动
- dream361@ubuntu16:~$ sudo update-rc.d mysql defaults
4.撤销随系统服务启动
- dream361@ubuntu16:~$ sudo update-rc.d -f mysql remove
5.与之前版本mysql不同,需要获得操作系统管理员权限,才能登录MariaDB的root用户,普通操作系统用户不能登录MariaDB数据库root用户
- dream361@ubuntu16:~$ sudo mysql -u root -p
6.备份mysql数据库 也需要获得操作系统管理员才能执行备份
- dream361@ubuntu16:~$ sudo mysqldump -uroot -p mysql >mysql.sql
7.创建普通数据库用户 (登录普通数据库用户则不需要获得操作系统管理员权限)
- create user 'zhangdc'@'%' identified by '1'; (后面数字是密码 可自行设置)
8.登录远地数据库(需要henry@'%')
- dream361@ubuntu16:~$ mysql -h localhost -u henry -p1
9.修改MariaDB配置文件,监听外网访问
- dream361@ubuntu16:~$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1 #注释掉这一行
10.重启数据库服务,使配置生效
- dream361@ubuntu16:~$ sudo systemctl restart mysql
11.MariaDB版本
-
- dream361@ubuntu16:~$ mysql -V
- mysql Ver 15.1 Distrib 10.0.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2