MySQL 数据库实操

2023-01-27 08:04:18

实现一下自动全量备份和增量备份组合

*/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是可以让对方访问的。

码农公寓