Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb
Enter password:
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'
mysql> grant file on *.* to 'backup'@'localhost';
Query OK, 0 rows affected (0.00 sec)
我们再次执行上面的备份命令
[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb
Enter password:
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=347;
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
可以很清楚地从提示看到是因为mysql服务启用了–secure-file-priv，所以才无法执行。 那么–secure-file-priv又是什么呢，应该如何解决才能是它可以备份呢？ --secure-file-priv=name ： Limit LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE() to files within specified directory
可以看到secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。
mysql> show global variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/home/mysql/data # mysql数据存放的目录
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_bin=mysql-bin
server-id=1
secure_file_priv =
再重启mysql服务
[root@localhost tmp]# systemctl restart mysqld
然后再查一下此时参数的值
mysql> show global variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | |
+--------------------------+-------+
3 rows in set (0.01 sec)
已经是我们要的结果 ，现在我们再次执行备份命令
[root@localhost tmp]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb
Enter password:
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
mysqldump: Got error: 1: Can't create/write to file '/tmp/mc_orderdb/order_cart.txt' (Errcode: 13 - Permission denied) when executing 'SELECT INTO OUTFILE'
结果还是权限被拒绝，无法写入，我们可以查询一下目录mc_orderdb的权限，
[root@localhost tmp]# ls -lh mc_orderdb/
total 4.0K
-rw-r--r-- 1 root root 1.9K Jan 10 10:51 order_cart.sql
可以发现，是root用户建立的目录，我们需要修改其所属用户为mysql用户，然后再次执行备份命令
[root@localhost tmp]# chown mysql:mysql mc_orderdb
[root@localhost tmp]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb
Enter password:
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
--
-- Dumping events for database 'mc_orderdb'
--
--
-- Dumping routines for database 'mc_orderdb'
--
[root@localhost tmp]#
[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --where "order_id>1000 and order_id<1050" mc_orderdb order_master > order_master_1000_1050.sql
Enter password:
查看备份文件可以发现，订单id是从1001开始的
2.4 定义备份脚本
在日程工作中我们不可能一直手工备份，所以我们需要将备份进行脚本话，然后使用计划任务去执行脚本
2.4.1 脚本文件
#!/bin/bash
###############Basic parameters##########################
DAY=`date +%Y%m%d` # 记录发生备份的当前日期
Environment=$(/sbin/ifconfig | grep "inet" | head -1 |grep -v "127.0.0.1" | awk '{print $2;}' ) # 当前主机的IP
USER="backup"
PASSWD="123456"
HostPort="3306"
MYSQLBASE="/home/mysql/"
DATADIR="/home/db_backup/${DAY}" # 备份存放的目录（/home/db_backup目录下的以日期命名的子目录中）
MYSQL=`/usr/bin/which mysql` # 定义mysql命令的目录
MYSQLDUMP=`/usr/bin/which mysqldump` # 定义mysqldump命令的目录
mkdir -p ${DATADIR} # 创建存储目录
# 定义备份函数，使用到上面定义的变量
Dump(){
${MYSQLDUMP} --master-data=2 --single-transaction --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort} ${database} > ${DATADIR}/${Environment}-${database}.sql
cd ${DATADIR}
gzip ${Environment}-${database}.sql # 对文件进行了压缩
}
# 利用for循环对当前服务器下的每一个数据库（排除了一些系统视图所在的数据库）分别来调用上面的Dump函数来进行备份
for db in `echo "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','sys','performance_schema')" | ${MYSQL} -u${USER} -p${PASSWD} --skip-column-names`
do
database=${db}
Dump
done
2.4.2 执行脚本文件
[root@localhost home]# bash backup.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost home]# cd db_backup/
[root@localhost db_backup]# ls
20190110 mc_orderdb.sql mc.sql order_master_1000_1050.sql order_master.sql
[root@localhost db_backup]# cd 20190110/
[root@localhost 20190110]# ls
172.17.0.1-mc_orderdb.sql.gz 172.17.0.1-mc_productdb.sql.gz 172.17.0.1-mc_userdb.sql.gz 172.17.0.1-mysql.sql.gz
mysql> DELETE FROM mc_orderdb.order_master LIMIT 10;
Query OK, 10 rows affected (0.01 sec)
mysql> SELECT COUNT(*) FROM mc_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
首先查出被误删的数据 SELECT a.* FROM bak_orderdb.order_master a LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id WHERE b.order_id IS NULL; 然后执行insert语句将查出的数据插入回去
完整语句为
INSERT INTO mc_orderdb.order_master(
order_id,order_sn,customer_id,shipping_user,province,city,district,address,
payment_method,order_money,district_money,shipping_money,payment_money,
shipping_comp_name,shipping_sn,create_time,shipping_time,pay_time,receive_time,
order_status,order_point,invoice_title,modified_time)
SELECT a.* FROM bak_orderdb.order_master a
LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id
WHERE b.order_id IS NULL;
执行结果：
mysql> INSERT INTO mc_orderdb.order_master(
-> order_id,order_sn,customer_id,shipping_user,province,city,district,address,
-> payment_method,order_money,district_money,shipping_money,payment_money,
-> shipping_comp_name,shipping_sn,create_time,shipping_time,pay_time,receive_time,
-> order_status,order_point,invoice_title,modified_time)
-> SELECT a.* FROM bak_orderdb.order_master a
-> LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id
-> WHERE b.order_id IS NULL;
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM mc_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
| 10010 |
+----------+
1 row in set (0.00 sec)
mysql> use mc_orderdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table t(id int auto_increment not null,uid int,cnt decimal(8,2),primary key (id));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t(uid,cnt) select customer_id,sum(order_money) from order_master group by customer_id;
Query OK, 6314 rows affected (0.05 sec)
Records: 6314 Duplicates: 0 Warnings: 0
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 6314 |
+----------+
1 row in set (0.00 sec)
mysql> delete from t limit 100;
Query OK, 100 rows affected (0.01 sec)
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 6214 |
+----------+
1 row in set (0.01 sec)
先恢复一个最近的全备，进行全量数据恢复
[root@localhost db_backup]# mysql -uroot -p mc_orderdb < mc_orderdb.sql
mysql> grant replication slave on *.* to 'repl'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
建立一个存储备份的二进制日志文件的目录
mkdir -p binlog_backup
最后在这个目录下，执行以下命令，就可以实时的进行二进制日志的备份了
[root@localhost binlog_bak]# mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -u repl -p123456 mysql-bin.000001
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
