mariadb数据库自动增加日期分区 mariadb创建数据库

 

MariaDB

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。

MariaDB直到5.5版本，均依照MySQL的版本。因此，使用MariaDB5.5的人会从MySQL 5.5中了解到MariaDB的所有功能。

从2012年11月12日起发布的10.0.0版开始，不再依照MySQL的版号。10.0.x版以5.5版为基础，加上移植自MySQL 5.6版的功能和自行开发的新功能。

MariaDB虽然被视为MySQL数据库的替代品，但它在扩展功能、存储引擎以及一些新的功能改进方面都强过MySQL。而且从MySQL迁移到MariaDB也是非常简单的：

• 数据和表定义文件（.frm）与二进制兼容
• 所有客户端API、协议和结构都完全一致
• 所有文件名、二进制、路径、端口等都一致
• 所有的MySQL连接器，比如PHP、Perl、Python、Java、.NET、MyODBC、Ruby以及MySQL C connector等在MariaDB中都保持不变
• mysql-client包在MariaDB服务器中也能够正常运行
• 共享的客户端库与MySQL也与二进制兼容

也就是说，在大多数情况下，你完全可以卸载MySQL然后安装MariaDB，然后就可以像之前一样正常的运行。

---

• 安装 Mariadb

配置安全脚本

安装完成后，root密码默认为空，因此需要进行配置安全控制程序。

在配置前需要先启动mariadb

---

使用 mysql_secure_installation 启动安全配置程序，输入当前 root 密码，因为当前密码为空，直接回车

---

是否设置 root 密码，这里输入 y 表示设置密码，输入2次密码，显示 success 为成功

---

是否删除匿名用户，选择删除

---

是否禁止 root 用户远程登录数据库，设置为是

---

是否删除test数据库，这里为了后面测试选择不删除

---

是否重载授权表，选择是

---

配置完成，下面使用 mysql 命令登录数据库

---

输入 help 可以得到帮助

---

使用 show databases; 可以查看所有数据库

---

使用 use 数据库名称; 进入数据库

---

使用 show tables; 查看表列表

---

使用 desc 表名称; 查看表结构

使用 select version(); 查看版本信息

---

输入 quit 或者 exit 退出数据库，使用 mysqladmin -u root -p version 也可以查看版本信息

---

数据库的日常操作不外乎增、改、删、查，下面我们一 一实践

• 创建新数据库 
•  create database 数据库名称;
• 指定默认字符 utf8
• create database DBNAME default charset 'utf8' collate 'utf8_bin';

• 删除数据库
• drop database 数据库名称;

---

• 创建新用户
• create user 'tom'@'localhost' identified by 'password';    'password' 为用户的口令
• （注：命令、参数不区分大小写，注意命令后面的分号  ; ）

• 授权用户访问数据库
• GRANT ALL PRIVILEGES ON mydata.* to '用户名'@'localhost';  授权某一用户使用 mydata 数据库
• GRANT ALL PRIVILEGES ON *.* to '用户名'@'localhost';     *.*代表所有数据库
• grant select,insert,delete,update on mydata.* to '用户名'@'localhost' identified by 'PASSWORD';  为用户授予部分权限

命令成功完成后需要刷新权限

• 删除用户
• Delete FROM mysql.user Where User='用户名' and Host='localhost';
• drop user '用户名'@'localhost'; 或者 drop user '用户名'@'%';   
• drop user ''@'localhost';

---

• 更改用户密码
• use mysql;
• update user set password=PASSWORD("new_password") where user="用户名";
• 或者
• alter user '用户名'@'IP' IDENTIFIED BY 'PASSWORD';
• flush privileges;     修改完密码必须更新权限

• 创建表格
• use mydata;   选择在哪个数据库内创建
• create table mylist (
  -> id int not null,
  -> name varchar(30),
  -> primary key (id));　　该字段列值唯一，"primary key" 表示该列是表的主键, MySQL将自动索引该列

查看表格创建过程

在 mylist 表中插入一条信息

更改表名称

删除表内数据

更改表里的内容

增加表里的条目

删除表里的条目

更改表里的列名

更改列数据类型

• 删除表格
• DROP TABLE table_name (删除表);

• 备份数据库
• mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql
• mysqldump -u root -p --all-databases > /Your_backup_PWD/all_dbs.sql    备份所有数据库

• 同时备份多个库
• mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 数据库名3 > 文件名.sql
• mysqldump -h192.168.10.2 -uroot -ppassword --databases mydata mysql > /Your_backup_PWD/db_mydata_mysql.sql

• 备份同个库多个表
• mysqldump -h主机名 -u用户名 -p密码 --database 数据库名 表1 表2 .... > 文件名.sql
• mysqldump -h192.168.10.2 -uroot -ppassword mydata mylist mylist1 > /Your_backup_PWD/mydata_mylist_mylist1.sql

• 备份数据库结构，不备份数据
• mysqldump --opt -d 数据库名 -u用户名 -p密码 > 文件名.sql     
•  -d只导出结构             -t只导出数据
• mysqldump --opt -d mydata > /Your_backup_PWD/structure.sql

• 恢复数据库
• 作为测试，在恢复之前先将原先的数据库删除
• mysql -uroot -p123456 -e "drop database mydata;"
• mysql -uroot -p123456 -e "show databases;"

• 恢复删除的数据库
• mysql -uroot -p123456 < /Your_backup_PWD/备份文件.sql

• 压缩备份
• mysqldump -uroot -p123456 -B mydata | gzip > /Your_backup_PWD/备份文件.sql.gz

• 恢复压缩备份
• gunzip < /Your_backup_PWD/备份文件.sql.gz | mysql -uroot -p123456

• 开启mysqlbinlog 实现增量备份
• 编辑 /etc/my.cof 配置文件，增加一行 log-bin=/data/mysql-bin 指明bin-log存放路径，注意/data目录的权限为 mysql.mysql
• 二进制日志文件与数据库数据文件最好不要放在同一块硬盘上，如果存放数据文件的硬盘坏了，可以用另一块硬盘的二进制日志来恢复数据

重新启动mariadb 查看 /data 目录是否有bin-log文件

• flush logs; 会多一个最新的bin-log日志
• show master status; 查看最后一个bin-log日志的相关信息
• reset master; 清空所有的bin-log日志

使用 mysqlbinlog 命令可以查看 bin-log 文件

• 使用二进制日志还原数据库
• mysqlbinlog /Your_Bin-log_DIR/mysql-bin.000001 | mysql -u root -p
• 这条命令可以理解为:使用mysqlbinlog读取二进制日志文件然后使用mysql命令还原到数据库中
• 注意还原时必须是编号小的先还原

• 删除二进制日志
• reset master； 删除所有
• purge master logs to ‘mysql-bin.000004‘;   删除编号000004以前的所有
• purge master logs to ‘2019-10-1 12:00:00‘;   删除指定日期时间之前的所有

• 查看mysql服务器是否开启binlog日志，查看binlog文件存放位置与名称

• 开启binlog之后，备份数据 -A是所有库 -l 是锁库的意思，-F 是备份成功后刷新binlog日志的意思，即生成一份新binlog日志文件
• mysqldump -uroot -p123456 -A -l -F > /Your_backup_PWD/备份文件名称.sql

---

主从复制

• 主数据库所在服务器IP:192.168.10.2
• 从数据库所在服务器IP:192.168.10.3
• mariadb的配置文件的位置：/etc/my.cnf

---

首先在主数据库端编辑 /etc/my.cnf

• [mysqld]
• log-bin=/data/mysql-bin                       # binlog二进制日志存放位置
• server-id=1                                          #主机标识ID号，不允许重复
• binlog-ignore-db=mysql                       # 指明  igonre-db 忽略同步的库
• binlog-ignore-db=information_schema
• binlog-ignore-db=performance_schema
• binlog-ignore-db=test
• innodb_flush_log_at_trx_commit=1
• binlog-do-db=database_name             #指明 do-db 需要同步的库
• replicate-do-db=database_name         #指明同步那些库
• lower_case_table_names=1               #关闭大小写敏感

[mysqld]
log-bin=/data/mysql-bin
server-id=1 
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
innodb_flush_log_at_trx_commit=1
binlog-do-db=database_name
replicate-do-db=database_name
lower_case_table_names=1

• 
  

mysql8.0 需要初始化时设置

• mysqld --user=mysql --lower-case-table-names=1 --initialize-insecure --datadir=/data/mysql
• 如果 /data/mysql 目录已经存在，需要手动删除
• 
  
• 添加一个用户 (slave) 并授权，重启 Mariadb 服务，查看记录log-bin文件名和pos号
• create user 'mycat'@'192.168.2.%' identified by 'MyCat@123456';
  grant all on *.* to 'mycat'@'192.168.2.%';
• 
  
• grant replication slave on *.* to '授权用户名'@'%' identified by '授权用户密码';
• GRANT ALL PRIVILEGES ON *.* TO 'mycat'@'%' IDENTIFIED BY '123456';
• flush privileges;      刷新权限

---

然后在从服务器配置 /etc/my.cnf

• log-bin=/data/mysql/mysql-bin                             指明 bin-log 二进制文件存放位置
• server-id=2                                                            指明主机标识ID号，不允许重复
• relay-log-index=/data/mysql/slave-relay-bin.index      定义relay_log的位置和名称
• relay-log=/data/mysql/slave-relay-bin

log-bin=/data/mysql/mysql-bin
server-id=2
relay-log-index=/data/mysql/slave-relay-bin.index
relay-log=/data/mysql/slave-relay-bin

---

重启从数据库服务后登陆进行以下操作

• stop slave;      停止slave复制
• grant replication slave on *.* to '授权用户名'@'%' identified by '授权用户密码';
• change master to master_host='主数据库IP地址',master_user='授权用户名',master_password='授权用户密码',master_log_file='主数据库BINLOG名称',master_log_pos=主数据库POS值;
• start slave;      开启slave复制
• show slave status\G      查看同步状况

• 出现一个报错
• ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log
• 查看 mariadb error log
• tail /var/log/mariadb/mariadb.log
• [ERROR] Failed to open the relay log '/data/slave-log.000036' (relay_log_pos 4)
• reset slave all;     清空 slave log 信息

导入主数据库备份文件

在主数据库端添加一些数据，观测从数据库是否同步

主主复制

• 首先在主数据库端编辑 /etc/my.cnf 添加以下内容
• log-bin=/data/master-log
• server-id=1
• relay-log=/data/slave-log
• auto_increment_offset=1            定义自增长字段开始数值
• auto_increment_increment=2     定义自增长字段每次递增的量值
• binlog_format=mixed     #混合模式复制(mixed-based replication, MBR)
  log-slave-updates=1      #控制是否把所有的操作写入到binary log，默认是关闭的
• max_binlog_size         = 100m              #binlog每个日志文件大小
  binlog_cache_size       = 4m                 #binlog缓存大小
  max_binlog_cache_size   = 512m        #最大binlog缓存大小

保存重启 mairadb 服务，登录后进行以下操作

• stop slave;      停止slave复制
• grant replication slave on *.* to '授权用户名'@'%' identified by '授权用户密码';
• change master to master_host='主数据库IP地址',master_user='授权用户名',master_password='授权用户密码',master_log_file='主数据库BINLOG名称',master_log_pos=主数据库POS值;
• start slave;      开启slave复制
• show slave status\G      查看同步状况

• 然后在 192.168.10.5 从服务器端做以下操作
• vim /etc/my.cnf 添加以下内容
• log-bin=/data/master-log
• server-id=2
• relay-log=/data/slave-log
• auto_increment_offset=2
• auto_increment_increment=2
• binlog_format=mixed     #混合模式复制(mixed-based replication, MBR)
  log-slave-updates=1      #控制是否把所有的操作写入到binary log，默认是关闭的
• max_binlog_size         = 100m              #binlog每个日志文件大小
  binlog_cache_size       = 4m                 #binlog缓存大小
  max_binlog_cache_size   = 512m        #最大binlog缓存大小

保存重启 mairadb 服务，登录后进行以下操作

• stop slave;      停止slave复制
• grant replication slave on *.* to '授权用户名'@'%' identified by '授权用户密码';
• change master to master_host='主数据库IP地址',master_user='授权用户名',master_password='授权用户密码',master_log_file='主数据库BINLOG名称',master_log_pos=主数据库POS值;
• start slave;      开启slave复制
• show slave status\G      查看同步状况

• 在2个服务器端分别添加、删除信息，验证同步是否正常
• 在 10.2 上添加一条信息

• 在 10.5 端查看

• 在 10.5 端删除一些信息

• 在 10.2 端查看

半同步复制

• 首先在主服务器端编辑 /etc/my.cnf 配置文件，添加以下内容
• plugin-load=rpl_semi_sync_master=semisync_master.so   启用半同步插件
• rpl_semi_sync_master_enabled=1     启用半同步复制

注：在某些高可用架构下，master和slave需同时启动，以便在切换后能继续使用半同步复制

• 即在主从数据库的my.cnf配置文件中都要添加:
• plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"rpl-semi-sync-master-enabled=1rpl-semi-sync-slave-enabled=1

• 在从服务器端编辑 /etc/my.cnf 配置文件，添加以下内容
• plugin-load=rpl_semi_sync_slave=semisync_slave.so
• rpl_semi_sync_slave_enabled=1

• 重启从数据库上的IO线程
• STOP SLAVE IO_THREAD;
• START SLAVE IO_THREAD;
• 注: 如果没有重启IO线程，则默认的还是异步复制模式

• 在主服务器端查看插件是否启用

• 登录后查看半同步状态
• show status like 'Rpl_semi_sync_master_status';

• 从数据库端查看
• show status like 'Rpl_semi_sync_slave_status';

• 接着测试一下是否为半同步复制
• 首先停止从数据库slave复制

• 在主数据库插入一些信息
• 发现第一条信息用了10.03sec， 第二条Eric0.01sec，其实Mysql半同步复制并不是严格意义上的半同步复制。当半同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制。[一旦有一次超时自动降级为异步].

• 在从数据库打开 slave 复制

• [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 log-bin=/data/mariadb/master-log
server-id=1
 relay-log=/data/mariadb/slave-log
auto_increment_offset=1
 auto_increment_increment=2
 binlog_format=mixed
 log-slave-updates=1
 max_binlog_size=100m
 binlog_cache_size=4m
 max_binlog_cache_size=512m
 binlog-ignore-db=mysql
 innodb_flush_log_at_trx_commit=1
 plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
 rpl-semi-sync-master-enabled=1
 rpl-semi-sync-slave-enabled=1• [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 log-bin=/data/mariadb/master-log
server-id=2
 binlog_format=mixed
 log-slave-updates=1
 relay-log=/data/mariadb/slave-log
auto_increment_offset=2
 auto_increment_increment=2
 max_binlog_size=100m
 binlog_cache_size=4m
 max_binlog_cache_size=512m
 binlog-ignore-db=mysql
 innodb_flush_log_at_trx_commit=1
 plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
 rpl-semi-sync-master-enabled=1
 rpl-semi-sync-slave-enabled=1• [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 log-bin=/data/mariadb/master-log
 server-id=3
 binlog_format=mixed
 log-slave-updates=1
 relay-log=/data/mariadb/slave-log
 auto_increment_offset=3                                                                                                                                                    
 auto_increment_increment=2
 max_binlog_size=100m
 binlog_cache_size=4m
 max_binlog_cache_size=512m
 binlog-ignore-db=mysql
 innodb_flush_log_at_trx_commit=1
 plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
 rpl-semi-sync-master-enabled=1
 rpl-semi-sync-slave-enabled=1

============================================================================================================

# rm -rf /etc/my.cnf
 # rm -rf /var/lib/mysql/*
 # chown -R  mysql.  /var/lib/mysql

---

• 忘记 mysql root 密码1、编辑文件：vim /etc/my.cnf2、在 [mysqld] 字段下面，按i键编辑；添加skip-grant-tables；按esc键，输入:wq保存退出3、重启mysql:sudo service mysqld restart第二步免密码登录mysql1、登录：mysql -u root -p2、提示输入密码按回车进入3、进入数据库，输入：flush privileges;use mysql；4、查看root用户信息：select host,user,authentication_string,plugin from user;5、更新root用户信息，把密码设置为空字符串：update user set authentication_string='' where user='root';第三步、退出mysql；注释掉/etc/my.cnf文件最后的 skip-grant-tables ；重启：sudo service mysqld restart 或者 systemctl restart mysqld第四步：设置密码1、重新开启一个客户端；2、登录mysql(这时候还是不用输入密码，因为上面已经把密码设置为空字符串了);3、修改root用户密码:ALTER user 'root'@'%' IDENTIFIED BY 'root123##ROOT';flush privileges;4、mysql8.0以上密码策略限制必须要大小写加数字特殊符号。退出mysql后就可以用密码登录了

• update user set host = "%" where user = "root";
select user,host,authentication_string from user;

---

[root@mysql1 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 binlog-ignore-db=mysql
 log-bin=mysql-bin-mastercharacter-set-client-handshake = FALSE
 character-set-server = utf8mb4
 collation-server = utf8mb4_unicode_ci
 init_connect='SET NAMES utf8mb4'server-id=12
 relay-log=slave-log
 binlog_format= ROW
auto_increment_offset=1
 auto_increment_increment=2
 log-slave-updates=1
 innodb_flush_log_at_trx_commit=2max_binlog_size = 100m 
 max_binlog_cache_size = 512m
 max_connections = 3000
 max_connect_errors = 3000
 table_open_cache = 2048
 max_allowed_packet = 16M
 binlog_cache_size = 16M
 max_heap_table_size = 16M
 tmp_table_size = 256M
 read_buffer_size = 1024M
 read_rnd_buffer_size = 1024M
 sort_buffer_size = 1024M
 join_buffer_size = 1024M
 key_buffer_size = 8M
 thread_cache_size = 8
 query_cache_type = on
 query_cache_size = 512M
 query_cache_limit = 1024M
 ft_min_word_len = 4
 long_query_time = 1 
 symbolic-links=0
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid
 validate-password=OFF
 lower_case_table_names=1
 [mysql]
 default-character-set = utf8mb4
 [client]
 default-character-set = utf8mb4

---

[root@mysql2 ~]# vim /etc/my.cnf 
 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 binlog-ignore-db=mysql
 log-bin=mysql-bin-mastercharacter-set-client-handshake = FALSE
 character-set-server = utf8mb4
 collation-server = utf8mb4_unicode_ci
 init_connect='SET NAMES utf8mb4'server-id=13
 relay-log=slave-log
 binlog_format= ROW
auto_increment_offset=2
 auto_increment_increment=2
 log-slave-updates=1
 innodb_flush_log_at_trx_commit=2max_binlog_size = 100m 
 max_binlog_cache_size = 512m
 max_connections = 3000
 max_connect_errors = 3000
 table_open_cache = 2048
 max_allowed_packet = 16M
 binlog_cache_size = 16M
 max_heap_table_size = 16M
 tmp_table_size = 256M
 read_buffer_size = 1024M
 read_rnd_buffer_size = 1024M
 sort_buffer_size = 1024M
 join_buffer_size = 1024M
 key_buffer_size = 8M
 thread_cache_size = 8
 query_cache_type = on
 query_cache_size = 512M
 query_cache_limit = 1024M
 ft_min_word_len = 4
 long_query_time = 1 
 symbolic-links=0
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid
 validate-password=OFF
 lower_case_table_names=1
 [mysql]
 default-character-set = utf8mb4
 [client]
 default-character-set = utf8mb4