首页 / MYSQL / MySQL 数据库实操
MySQL 数据库实操
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 数据库实操,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5484字,纯文字阅读大概需要8分钟。
内容图文
![MySQL 数据库实操](/upload/InfoBanner/zyjiaocheng/872/b81959d290004dcca400aad318123987.jpg)
实现一下自动全量备份和增量备份组合
*/10 * * * * mysqldump -uroot -p123456 --databases icoding_admin > /usr/local/bak/bak.sql 2>&1
*/1 * * * * mysqladmin -uroot -p123456 flush-logs
利用crontab -e 将上述crontab的命令安装到系统,为了测试需要,每分钟执行一次增量备份,每十分钟执行一次全量,执行结果如下:
增量:
[root@iZuf6goyq9ypxyqykpq0wwZ binlog]# ls -alt
total 48
drwxr-xr-x 3 mysql mysql 262 Jun 23 14:51 .
-rw-r----- 1 mysql mysql 315 Jun 23 14:51 mysql-bin.index
-rw-r----- 1 mysql mysql 154 Jun 23 14:51 mysql-bin.000009
-rw-r----- 1 mysql mysql 201 Jun 23 14:51 mysql-bin.000008
-rw-r----- 1 mysql mysql 201 Jun 23 14:50 mysql-bin.000007
-rw-r----- 1 mysql mysql 201 Jun 23 14:49 mysql-bin.000006
全量:
14:50分执行了一次,
[root@iZuf6goyq9ypxyqykpq0wwZ bak]# ls -alt
total 8
-rw-r--r-- 1 root root 5548 Jun 23 14:50 bak.sql
drwxr-xr-x 2 mysql mysql 21 Jun 23 14:39 .
drwxr-xr-x. 16 mysql mysql 198 Jun 23 14:18 ..
15:00又执行了一次:
[root@iZuf6goyq9ypxyqykpq0wwZ bak]# ls -alt
total 8
-rw-r--r-- 1 root root 5548 Jun 23 15:00 bak.sql
drwxr-xr-x 2 mysql mysql 21 Jun 23 14:39 .
drwxr-xr-x. 16 mysql mysql 198 Jun 23 14:18 ..
这个link https://www.jianshu.com/p/d3f77f7da512 已经写的比较完备,里面的Scripts可以在生产环境上使用。
根据自己公司的业务设计一套数据库设计规范标准
见《阿里巴巴Java开发手册泰山版》第五章。 没有补充了。
如果我们要删除分区前需要进行数据归档,如何来做,自己做一下
几种方法:
- The Percona xtrabackup solution
这个先放在一边。
- 手动根据mysql的命令来操作
这里也有两种方式,比较完备的方式是温备+归档,另外一种就是简单的利用exchange partition来直接备份了。
先说利用exchange partition来直接备份的方式:
Exchanging a Partition with a Nonpartitioned Table 2:
In the same DB and without Read locks
0) create DB and tables
CREATE TABLE origintable (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (150),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO origintable VALUES
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");
CREATE TABLE backuptable LIKE origintable;
ALTER TABLE backuptable REMOVE PARTITIONING;
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'origintable';
ALTER TABLE origintable EXCHANGE PARTITION p0 WITH TABLE backuptable;
ALTER TABLE origintable drop PARTITION p0;
然后温备+归档方式的步骤:
In different DB and with Read locks
1)create a DB and Backup DB:
create database customer;
create database customer_bk;
2)Create tables
create tables in DB customer with 4 partitions.
create tables in DB customer_bk without partitions.
3) inject data into tables.
into DB customer's tables
4) copy the data into destination folder with reading lock on the whole table of customer DB.
5)import the data into the tables in the backup database.
6)check the data in the backup database.
7)release the reading lock.
6月21日数据库高级课程继续
搭建一个双主复制的结构
Master A:
配置id策略+给user权限:
# master to master replication
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates
mysql> grant replication slave on *.* to 'repluser'@'[MasterA IP]' identified by '123456';
mysql> flush privileges;
Master B:
配置id策略+给user权限:
# master to master replication
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates
mysql> grant replication slave on *.* to 'repluser'@'[MasterB IP]' identified by '123456';
mysql> flush privileges;
在Master A and B上查看Master的信息:
Master A:
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-bin2.000004 | 863 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> change master to master_host='[MasterB IP]',master_port=3306,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=1020;
mysql> start slave;
mysql> show slave status\G
Master B:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 1020 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> change master to master_host='[MasterA IP]',master_port=3306,master_user='repluser',master_password='123456',master_log_file='mysql-bin2.000004',master_log_pos=863;
mysql> start slave;
mysql> show slave status\G
测试两主之间的复制:
Master B: 创建一个Databases
mysql> create database testrelication2;
Query OK, 1 row affected (0.01 sec)
Master A: 复制这个database成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testrelication |
| testrelication2 |
+--------------------+
6 rows in set (0.01 sec)
反向也能成功,这里一定要注意两个Master之间3306是可以让对方访问的。
内容总结
以上是互联网集市为您收集整理的MySQL 数据库实操全部内容,希望文章能够帮你解决MySQL 数据库实操所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。