本文详细介绍了Mariadb的数据转移方法，包括数据库备份和sql语句执行。同时，深入探讨了Mariadb主从复制的配置，包括master和slave的设置、主从复制的重置，并讲解了数据库默认编码、用户管理及权限设置。此外，还特别讨论了如何将master作为CloudStack的连接数据库进行主从复制配置。
Mariadb可以看做mysql的分支，操作基本和mysql一致。
数据转移
只是将数据从一个节点的数据库转移到另一个节点的数据库。。。
先将数据库备份成一个sql文件，再在其它节点执行该sql完成数据库数据转移。
数据库备份。
将mariadb中的数据库库备份成一个sql执行文件。
[root@mariadb-server1 ~]# mysqldump -help
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
可以备份
- 单个数据库 直接指定数据库名
- 多个数据库 --databases 指定多个数据库名
- 所有数据库 --all-databases
示例1 只备份cloud_usage数据库。
mysqldump -u root -p cloud_usage > cloud_usage.sql
输入密码即可开始备份
cloud_usage.sql 为备份的sql文件，文件名可以任意，也不用是sql后缀。
只备份单个数据库的sql文件不会说明使用的哪个数据库，因此在其它节点数据库执行时要指明数据库。
示例2 备份所有的数据库
mysqldump -u root -p --all-databases > alldatabases.sql
会将数据库所有内容写入sql 文件。
sql 文件大致内容
[root@mariadb-server1 ~]# head -30 cloud_usage.sql
-- MySQL dump 10.14 Distrib 5.5.65-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: cloud_usage
-- ------------------------------------------------------
-- Server version 5.5.65-MariaDB
--
-- Table structure for table `account`
--
DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account` (
`id` bigint(20) unsigned NOT NULL,
`account_name` varchar(100) DEFAULT NULL COMMENT 'an account name set by the creator of the account, defaults to username for single accounts',
`uuid` varchar(40) DEFAULT NULL,
`type` int(1) unsigned NOT NULL,
`domain_id` bigint(20) unsigned DEFAULT NULL,
大约就是一些 数据库表的增删语句，不过单个数据库的sql备份没有use 语句指明数据库
[root@mariadb-server1 ~]# head -30 alldatabases.sql
-- MySQL dump 10.14 Distrib 5.5.65-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.65-MariaDB
--
-- Current Database: `cloud`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cloud` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `cloud`;
--
-- Table structure for table `account`
--
DROP TABLE IF EXISTS `account`;
多个数据库的sql 文件存在use 语句，指明了操作哪个数据库。
sql语句执行 两种方式
方式一 如果sql脚本指定了数据库 -D 参数可以省略
mysql –u用户名 –p密码 –D数据库 <[sql脚本文件路径全名]
如
mysql -u root -p-D cloud_usage < ./cloud_usage.sql
再输入密码即可
方式二，进入MySQL命令行，执行source命令。如果sql文件未指定数据库，需提前使用 use 数据库 指令指定数据库
mysql –u用户名 –p密码
source [sql脚本文件的路径全名]
Mariadb 主从备份
概念转自他人
MySQL之间数据复制的基础是以二进制日志文件（binary log file）来实现的，一台MySQL数据库一旦启用二进制日志后，其作为master，它数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
实现MySQL主从复制配置要求：
主服务器：1、开启数据库二进制日志功能；2、配置数据库认证唯一服务id；3、获得主库的二进制日志文件名及位置；4、在主库上面创建一个用于主库和从库通信的用户账号，安全管理。
从服务器：1、在从库中配置唯一服务id；2、使用主库创建分配的用户账号读取主库的二进制日志；3、启用slave功能，用于主从通信。
作者：易霂
链接：https://www.jianshu.com/p/fb404e2a681b
来源：简书
测试一主二从备份。准备三个虚拟机
192.168.199.81 mariadb-server1
192.168.199.82 mariadb-server2
192.168.199.83 mariadb-server3
安装 mariadb 服务
yum -y install mariadb-server
mariadb-server1 作为主机master 其余两个作为从机 slave。一般设置主从备份，主机用来写数据，客户端从salve读取数据。
在配置主从复制之前作为master的的数据库中有数据的话，主从复制是不会将之前的数据写入到从机的。如果希望主从数据库数据保持一致，需要先将主数据库的内容备份到从数据库。采用上面的数据转移方法即可
master主机192.168.199.91 master设置
编辑mariadb配置文件
vi /etc/my.cnf
在mysqld 模块中加入
bind-address = 0.0.0.0
log-bin = mysql-bin
server-id = 1
bind-address = 0.0.0.0 表示允许其他主机连接到本数据库
log-bin
有三个作用
一 开启了 二进制日志
二 设置了二进制日志文件名，未设置目录 默认使用 /var/lib/mysql/ 目录
三 设置了 二进制日志索引文件 .index。文件名master-bin.index 路径 同上/var/lib/mysql/master-bin.index
log-bin-index 可单独指定 mysql二进制日志索引文件。如log-bin-index = master-bin.index
server-id = 1 设置 数据库中唯一ID 主从复制中该ID唯一。
尽管只设置上面的就可以构建主从复制中master设置，但一般还需要一些优化设置
innodb-file-per-table =ON # 拆分表数据存储
skip_name_resolve=ON # 跳过IP反向解析。可节省资源
binlog-ignore-db=information_schema,performance_schema # 可选 不进行同步的数据库
expire_logs_days = 10 # 自动清除10天前的二进制日志文件
expire_logs_days = 10 # 自动清除10天前的二进制日志文件
由于启用二进制日志。有时候存储东西并不多，但是二进制日志使用空间却很大。为了避免空间不足，可以设置自动清除n天前的日志文件。不设置默认为0 表示不清除。
日志文件默认最大为1G 超出该大小就会创建另一个日志文件
max_binlog_size
重启数据库
systemctl restart mariadb
可以查看下二进制日志文件路径
cd /var/lib/mysql/
可以看到 二进制日志文件mysql-bin.000001 索引文件mysql-bin.index
进入数据库控制台
mysql -u root -p
创建一个用于主从复制的 用户 并赋予对所有库的所有表复制权限。
账号 feng 密码123456 所有网段有效有效
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'feng'@'%' IDENTIFIED BY '123456';
刷新使生效
flush privileges;
查看master状态，已经开启二进制日志文件了。
show master status;
记录下 File 和 Position。在设置从服务器是需要使用。
File和Position在master数据库改变数据后是会改变的。如果一个slave不是一开始就加入的，最好要先将master主机数据库的数据先复制一遍。见上文。 或者 记得主从复制开启时File和Position的 初值，将初值填入从机设置。
slave从机 192.168.199.82 192.168.199.83设置
192.168.199.81编辑配置文件。
只用设置惟一的 sever-id
vi /etc/my.cnf
server-id = 2
尽管只用设置server-id就可以当做 主从配置slave 配置。但一般配置的更多一点
skip_name_resolve = ON
innodb_file_per_table = ON
relay_log = slave_relay_bin # 设置中继日志文件名
relay_log_recovery=1
relay_log_purge=1
relay_log = slave_relay_bin # 设置中继日志文件名
通 二进制日志类似，开启中继日志，设置中继日志名。
relay_log_recovery=1
当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log。
并且重新从master上获取日志，这样就保证了relay-log的完整性。
默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。
relay_log_purge=1
中继日志文件自动清除，无需设置，默认开启即为1
重启mariadb服务
systemctl restart mariadb
进入数据库控制台
mysql -u root -p
查看 salve 配置，默认为空。
show slave status\G
进行设置。
CHANGE MASTER TO MASTER_HOST="主机IP",
MASTER_USER="账户",
MASTER_PASSWORD="密码",
MASTER_LOG_FILE="主机二进制日志文件",
MASTER_LOG_POS=具体值;
MASTER_LOG_FILE MASTER_LOG_POS同刚才在 master 主机查看到的file 和 pos 一致。
CHANGE MASTER TO MASTER_HOST="192.168.199.81",
MASTER_USER="feng",
MASTER_PASSWORD="123456",
MASTER_LOG_FILE="mysql-bin.000001",
MASTER_LOG_POS=245;
设置完成，查看 slave
show slave status\G
可以看到设置后slave不为空了，但是slave未开启，因此
Slave_IO_Running: No
Slave_SQL_Running: No
只有Slave_IO_Running Slave_SQL_Running运行，从机才会正常复制主服务器数据 因此需要启动slave
启动 slave
start slave;
再次查看slave状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从机设置完成。。。
这时候查看 mysql 默认存储目录
可以看到中继日志文件名就是 刚才设置的 slave_relay_bin。
且可以发现 master relay-log 等信息
查看中继日志信息
可以看到中继日志信息记录了 中继日志 文件 和 位置 以及 master 二进制日志文件和位置
192.168.199.83进行基本相同设置。
注意server-id 不能一致 可设置为3
vi /etc/my.cnf
server-id = 3
其他一致
测试主从复制使用
设置完毕，可以测试一下是否成功。
在192.168.199.81 节点master数据库添加数据。查看两个slave节点数据库是否有新增内容。
mysql -u root -p
创建数据库 hero 使用该数据库 创建表hero 添加值
create database hero;
use hero;
create table hero(id int primary key auto_increment, heroName varchar(10) not null, age int default 18);
insert into hero values(0, "张无忌", 20);
insert into hero values(0, "郭靖", 20);
insert into hero values(0, "杨过", 20);
可以查看192.168.199.82 或 192.168.199.83的数据库。果然多了hero数据库
可以看到从库增加了相应的库和表及数据。（数据没看，可以自己测试。。。）
主从复制重置
master
reset master;
slave
reset slave;
然后重新查看master的文件和 POS，设置slave并启动。
mariadb数据库默认编码
show variables like 'character%';
character_set_database
mariadb 数据库的默认编码是latin1,不支持中文。
如果要设置 修改默认编码，需要修改三个文件配置。
vi /etc/my.cnf.d/client.cnf
在[client] 模块加上
default-character-set=utf8
vi /etc/my.cnf.d/mysql-clients.cnf
在[mysql]模块加上
default-character-set=utf8
vi /etc/my.cnf.d/server.cnf
在[mysqld]模块加上
character-set-server=utf8
修改完成重启 mariadb数据库
systemctl restart mariadb
默认编码已经变成了 utf8
增加用户及设置访问权限
原作者: Apollo
博客
https://www.cnblogs.com/apollo1616/articles/10294490.html
原作者: IT陈工
博客
https://blog.51cto.com/13873498/2298379
创建用户
命令
CREATE USER 'username'@'host'[IDENTIFIED BY 'password'];
参数
username:
创建的用户名
host:
指定该用户在哪个主机上可以登陆,
如果是本地用户可用localhost,
如果想让该用户可以从任意远程主机登陆,可以使用通配符%
password:
该用户的登陆密码,密码可以为空,
如果为空则该用户可以不需要密码登陆服务器.
IDENTIFIED BY ‘password’ 省略就表示该用户登录不需要输入密码。
默认权限USAGE
USAGE：
无权限 只有登陆连接数据库的权限。
实例一：
CREATE USER 'feng1'@'192.168.199.90' IDENTIFIED BY '123456';
创建一个用户feng1 密码123456 只允许在主机192.168.199.90 登陆
实例二：
CREATE USER 'feng2'@'%' IDENTIFIED BY '123456';
创建一个用户feng2 密码123456 允许在所有主机登陆
实例三：
CREATE USER 'feng3'@'192.168.199.%' IDENTIFIED BY '123456';
创建一个用户名feng3 密码123456 允许在192.168.199段的主机上登陆
查看数据库用户，一般只查看用户名 和 可登陆主机
select user,host from mysql.user;
这三个用户feng1 feng2 feng3目前都是无任何权限的。无法使用它们创建数据库建表等操作。
查看当前用户
select user();
综上，可以看做mariadb中用户并不是以用户名单独存在的，而是用户名加上可登录主机构成了一个用户 ‘username’@‘host’ 不把username当成一个用户，这样理解起来更方便。 一个用户只能通过一个密码登陆进数据库服务器，密码可以为空，但是不可多个。
设置权限
命令
GRANT privileges ON databasename.tablename TO 'username'@'host' [IDENTIFIED BY 'password'];
参数
privileges:
用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL.
databasename:
数据库名
tablename:
表名，* 表示所有表
如果要授予该用户对所有数据库和表的相应操作权限，则可用*.*表示
‘username’@‘host’ 表示一个 用户
很多情况下，会跳过创建用户的一步，直接使用设置权限命令可以创建用户,还可以修改已存在用户密码
用户可设置的权限，具体解释还是网上搜比较靠谱
管理类：
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS
程序类:FUNCTION(函数),PROCEDURE(存储过程),TRIGGER（触发器）
CREATE
ALTER
DROP
EXCUTE
数据库和级别:DATABASE(数据库),TABLE(表)
ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能将自己获得的权限转赠给其他用户
数据操作：
SELECT
INSERT
DELETE
UPDATE
字段级别：
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
所有权限：
ALL PRIVILEGES 或者 ALL
实例一：
GRANT CREATE,ALTER,DROP ON *.* TO 'feng1'@'192.168.199.90';
赋予用户 ‘feng1’@‘192.168.199.90’ 对所有库表的CREATE,ALTER,DROP权限，权限可以是单个，多给权限之间 , 隔开。
实例二：
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'feng2'@'%' WITH GRANT OPTION;
赋予用户 ‘feng2’@’%’ 对所有库表的 REPLICATION SLAVE, REPLICATION CLIENT 权限，多用于主从复制时权限设置。末尾加上WITH GRANT OPTION表示该用户可以将权限赋予其它用户 ，同在设置权限列表中加上GRANT OPTION 效果是一致的。
实例三：
GRANT ALL PRIVILEGES ON *.* TO 'feng3'@'192.168.199.%';
赋予用户’feng3’@'192.168.199.%'所有权限。
实例四：
GRANT ALL PRIVILEGES ON *.* TO 'feng4'@'%' IDENTIFIED BY '123456';
由于本来不存在用户 ‘feng4’@’%’ 所以该指令会先创建用户，然后再赋予该用户所有权限。
权限设置后有可能刷新不及时等不能立即生效。需要手动刷新
FLUSH PRIVILEGES;
查看权限
查看帮助
help show grants
查看当前登录用户权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
MariaDB [(none)]> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
查看指定用户权限
SHOW GRANTS [FOR user]
如果只指定了 用户名 username 未指定host 默认host 为 ‘%’ 如果没有该用户查看权限失败
SHOW GRANTS FOR 'feng1'@'192.168.199.90';
SHOW GRANTS FOR 'feng2'
移除权限
命令
REVOKE privileges ON databasename.tablename FROM 'username'@'host';
参数
privileges:
用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL.
databasename:
数据库名
tablename:
表名,如果要取消该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
同赋予权限操作命令GRANT类似只是 命令变成了REVOKE，TO 变成了 FROM，该命令不能创建 新用户和更改已有用户密码
特殊处是*.*,如果赋予权限时作用对象是*.*移除权限时作用对象是某一具体的库表，或者赋予权限是作用对象是某一具体的库表移除时作用对象是*.*，那么移除权限的命令不能生效。
实例一
REVOKE DROP, ALTER ON *.* FROM 'feng1'@'192.168.199.90';
删除用户
mariadb中存储用户的 数据表是 mysql 库下的 user 表。可以通过修改该表内容改变数据库。
一般用该表进行用户查看
查看 所有 用户 的 user, host 一般只用查看这两个字段。
select user,host from mysql.user;
可以修改删除表中数据来进行用户改变，但一般不使用这种方法
删除用户 ‘feng4’@’%’
delete from mysql.user where user='feng4' and host='%';
修改用户同理，不再举例，但是用户删除修改一般不用修改表的方式实现。
有专门的的命令来进行操作
删除用户命令
查看帮助
drop user help;
可以看到删除用户命令
DROP USER user [, user] ...
user是一个’username’@‘host’，如果只有用户名 那么便会有默认的 host %
实例
DROP USER 'feng3'@'192.168.199.%';
DROP USER 'feng1'@'192.168.199.90';
重命名用户
help rename user;
RENAME USER old_user TO new_user
[, old_user TO new_user] ...
可以一次重命名多个。依然是指定’username’@‘host’ 未指定host默认就是%,不同的就是这个规则使用与两个user，不管新旧。
实例
RENAME USER 'feng2' TO 'feng';
mariadb主从复制设置master当作CloudStack连接的数据库。
master 主机节点数据库设置。
vi /etc/my.cnf
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format = 'ROW'
bind-address = 0.0.0.0
server-id = 1 #唯一ID
innodb-file-per-table =ON # 拆分表数据存储
skip_name_resolve=ON # 跳过IP反向解析。可节省资源
expire_logs_days = 10 # 自动清除10天前的二进制日志文件
设置完毕重启数据库
systemctl restart mariadb
创建一个用于主从复制的 用户 并赋予对所有库的所有表复制权限。
账号 feng 密码123456 所有网段有效有效
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'feng'@'%' IDENTIFIED BY '123456';
刷新使生效
flush privileges;
reset master;
查看 master 的状态，记录下File 和 Position
show master status;
slave 从机设置
vi /etc/my.cnf
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format = 'ROW'
server-id = 2
skip_name_resolve = ON
innodb_file_per_table = ON
relay_log = slave_relay_bin
relay_log_recovery=1
relay_log_purge=1
设置完毕重启数据库
systemctl restart mariadb
多个从机设置只要 server-id 不同即可
设置slave
mysql -u root -p
CHANGE MASTER TO MASTER_HOST="主机IP",
MASTER_USER="账户",
MASTER_PASSWORD="密码",
MASTER_LOG_FILE="主机二进制日志文件",
MASTER_LOG_POS=具体值;
如
CHANGE MASTER TO MASTER_HOST="192.168.199.81",
MASTER_USER="feng",
MASTER_PASSWORD="123456",
MASTER_LOG_FILE="mysql-bin.000002",
MASTER_LOG_POS=245;
启动slave
start slave;
master创建cloudstack所需用户
mysql -u root -p
创建cloud 用户
GRANT PROCESS ON *.* TO 'cloud'@'localhost' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON `cloud`.* TO 'cloud'@'localhost' ;
GRANT ALL PRIVILEGES ON `cloud_usage`.* TO 'cloud'@'localhost';
GRANT PROCESS ON *.* TO 'cloud'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON `cloud`.* TO 'cloud'@'%';
GRANT ALL PRIVILEGES ON `cloud_usage`.* TO 'cloud'@'%';
cloudstack如果初始化数据选择master节点
cloudstack-setup-databases cloud:123456@master --deploy-as=root:123456
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
