mysqldump和mysqlbinlog

2017-01-05 11:17:48来源:oschina作者:neal_ke人点击



##操作
- 连接MySQL客户端
[root@nbview ~]# ps -ef |grep mysql
root4055 10 Jul22 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/my3306.cnf
mysql 510840550 Jul22 ? 00:03:23 /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my3306.cnf --basedir=/usr/local/mysql --datadir=/data1/db3306/ --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data1/db3306/error.log --pid-file=/data1/db3306/mysql.pid --socket=/data1/db3306/my3306.sock --port=3306
root5395 10 Jul22 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/my3307.cnf
mysql 644853950 Jul22 ? 00:03:34 /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my3307.cnf --basedir=/usr/local/mysql --datadir=/data1/db3307/ --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data1/db3307/error.log --pid-file=/data1/db3307/mysql.pid --socket=/data1/db3307/my3307.sock --port=3307
root 11398 112120 13:07 pts/000:00:00 grep mysql
[root@nbview ~]# mysql -S /data1/db3306/my3306.sock
- 显示数据库
mysql> show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| mysql|
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
- 创建数据库
mysql> create database student_db1;
- 切换数据库
mysql> use student_db1;
Database changed
- 创建一张表
mysql> create table tab1(id int not null auto_increment,t_name varchar(32),primary key(id));
Query OK, 0 rows affected (0.10 sec)
- 查看刚创建的表
mysql> show create table tab1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab1| CREATE TABLE `tab1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 查看创建的表
mysql> show create table tab1/G;
*************************** 1. row ***************************
Table: tab1
Create Table: CREATE TABLE `tab1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
- 插入数据
mysql> insert into tab1(t_name) values('Tom'),('Jerry');
Query OK, 2 rows affected (0.00 sec)
Records: 2Duplicates: 0Warnings: 0
- 备份数据
- 查看日志是否打开,如果没有打开,则打开
mysql> show global variables like '%gen%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:33
Current database: student_db1
+------------------+--------------------------+
| Variable_name| Value|
+------------------+--------------------------+
| general_log| OFF|
| general_log_file | /data1/db3306/nbview.log |
+------------------+--------------------------+
2 rows in set (0.01 sec)
mysql> set general_log = on;
ERROR 1229 (HY000): Variable 'general_log' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global general_log = on;
Query OK, 0 rows affected (0.02 sec)
mysql> show global variables like '%gen%';
+------------------+--------------------------+
| Variable_name| Value|
+------------------+--------------------------+
| general_log| ON|
| general_log_file | /data1/db3306/nbview.log |
+------------------+----------
[root@nbview ~]# vim /tmp/student_db1.sql
-- MySQL dump 10.13Distrib 5.6.30, for linux-glibc2.5 (x86_64)
--
-- Host: localhostDatabase: student_db1
-- ------------------------------------------------------
-- Server version5.6.30-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='187588dc-4fb3-11e6-9a91-00163e0131f0:1-3';
----------------+
2 rows in set (0.01 sec)
-查看日志文件存放的内容
[root@nbview ~]# vim /data1/db3306/nbview.log
/usr/local/mysql/bin/mysqld, Version: 5.6.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306Unix socket: /data1/db3306/my3306.sock
Time Id CommandArgument
160725 13:29:2333 Query show global variables like '%gen%'
160725 13:31:1834 Connect root@localhost on student_db1
34 Query show databases
34 Query show tables
34 Field List tab1
34 Query select * from tab1
-备份
1、首先对单个库进行备份
[root@nbview ~]# mysqldump --single-transaction -B student_db1 > /tmp/student_db1.sql -S /data1/db3306/my3306.sock
-S /data1/db3306/my3306.sock 连接到指定的实例
[root@nbview ~]# mysqldump --single-transaction -B student_db1 > /tmp/student_db1.sql
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
[root@nbview ~]# mysqldump --single-transaction -B student_db1 > /tmp/student_db1.sql -S /data1/db3306/my3306.sock
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@nbview ~]#
2、查看备份是否执行
[root@nbview ~]# vim /tmp/student_db1.sql
-- MySQL dump 10.13Distrib 5.6.30, for linux-glibc2.5 (x86_64)
--
-- Host: localhostDatabase: student_db1
-- ------------------------------------------------------
-- Server version5.6.30-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='187588dc-4fb3-11e6-9a91-00163e0131f0:1-3';复制服务器中所有的库
[root@nbview db3306]# mysqldump --single-transaction --master-data=2 -S /data1/db3306/my3306.sock -A > /tmp/full_mysql.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@nbview db3306]# vim nbview.log
160725 14:01:4236 Connect root@localhost on
36 Query /*!40100 SET @@SQL_MODE='' */
36 Query /*!40103 SET TIME_ZONE='+00:00' */
36 Query FLUSH /*!40101 LOCAL */ TABLES(将缓存中的数据持久化)
36 Query FLUSH TABLES WITH READ LOCK
36 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ(设置隔离级别为可重复读)
36 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */(开启一致性快照,即对历史性的数据进行镜像)
36 Query SHOW VARIABLES LIKE 'gtid/_mode'
36 Query SELECT @@GLOBAL.GTID_EXECUTED
36 Query SHOW MASTER STATUS
36 Query UNLOCK TABLES
36 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
36 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
36 Query SHOW DATABASES(查看所有的数据库)
36 Query SHOW VARIABLES LIKE 'ndbinfo/_version'
36 Init DB mysql
36 Query SHOW CREATE DATABASE IF NOT EXISTS `mysql`
36 Query SAVEPOINT sp(创建保存点)
36 Query show tables (查询库里有多少表)
36 Query show table status like 'columns/_priv'
36 Query SET SQL_QUOTE_SHOW_CREATE=1
36 Query SET SESSION character_set_results = 'binary'
36 Query show create table `columns_priv`
36 Query SET SESSION character_set_results = 'utf8'
36 Query show fields from `columns_priv`
36 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `columns_priv`(从持久盘里查询数据)
36 Query SET SESSION character_set_results = 'binary'
36 Query use `mysql`
36 Query select @@collation_database
36 Query SHOW TRIGGERS LIKE 'columns/_priv'
36 Query SET SESSION character_set_results = 'utf8'
36 Query ROLLBACK TO SAVEPOINT sp(回滚到保存点)
36 Query show table status like 'db'
36 Query SET SQL_QUOTE_SHOW_CREATE=1
36 Query SET SESSION character_set_results = 'binary'
36 Query show create table `db`
36 Query SET SESSION character_set_results = 'utf8'
36 Query show fields from `db`
36 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `db`
36 Query SET SESSION character_set_results = 'binary'
36 Query use `mysql`
36 Query select @@collation_database
36 Query SHOW TRIGGERS LIKE 'db'
36 Query SET SESSION character_set_results = 'utf8'
36 Query ROLLBACK TO SAVEPOINT sp
36 Query show table status like 'event'
36 Query SET SQL_QUOTE_SHOW_CREATE=1
36 Query SET SESSION character_set_results = 'binary'
36 Query show create table `event`
36 Query SET SESSION character_set_results = 'utf8'
36 Query show fields from `event`
36 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `event`
single-transaction 的重要性:创建快照##备份模板
mysqldump -S /data1/db3341/my3341.sock (连接)
--default-character-set=utf8 (指定字符集)
--quick(从表里读取,不从缓存里获取)
--routines (备份存储过程)
--trigger (备份trigger)
--events(备份events)
--single-transaction (快照读)
-B xxx(备份某个库) > /data1/mysqlbackup/xxxx.sql[root@nbview db3306]# mysqlcheck -S /data1/db3306/my3306.sock -B mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.funcOK
mysql.general_logOK
mysql.help_categoryOK
mysql.help_keyword OK
mysql.help_relationOK
mysql.help_topicOK
mysql.innodb_index_statsOK
mysql.innodb_table_statsOK
mysql.ndb_binlog_index OK
mysql.pluginOK
mysql.procOK
mysql.procs_privOK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_infoOK
mysql.slave_relay_log_infoOK
mysql.slave_worker_infoOK
mysql.slow_logOK
mysql.tables_privOK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transitionOK
mysql.time_zone_transition_typeOK
mysqlcheck -r(repair)只适合mysaim
[root@nbview db3306]# mysqlcheck -S /data1/db3306/my3306.sock -r -B mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.funcOK
mysql.general_logOK
mysql.help_categoryOK
mysql.help_keyword OK
mysql.help_relationOK
mysql.help_topicOK
mysql.innodb_index_stats
note : The storage engine for the table doesn't support repair
mysql.innodb_table_stats
note : The storage engine for the table doesn't support repair
mysql.ndb_binlog_index OK
mysql.pluginOK
mysql.procOK
mysql.procs_privOK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info
note : The storage engine for the table doesn't support repair
mysql.slave_relay_log_info
note : The storage engine for the table doesn't support repair
mysql.slave_worker_info
note : The storage engine for the table doesn't support repair
mysql.slow_logOK
mysql.tables_privOK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transitionOK
mysql.time_zone_transition_typeOK
##查看binlog日志
mysql> show master status/G;
*************************** 1. row ***************************
File: 3306-mysql-bin.000009
Position: 4083
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 187588dc-4fb3-11e6-9a91-00163e0131f0:1-26
1 row in set (0.00 sec)
ERROR:
No query specified
接着查看binlog的格式,此处binlog_format的格式是row
mysql>show global variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value|
+--------------------------+-------------------+
| binlog_format | ROW |
| date_format| %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format| 0 |
| innodb_file_format| Barracuda|
| innodb_file_format_check | ON|
| innodb_file_format_max | Antelope |
| time_format| %H:%i:%s |
+--------------------------+-------------------+
8 rows in set (0.00 sec)1、复制binlog方案一:此方法复制的DML语句都是加密过的
[root@nbview db3306]# mysqlbinlog -v 3306-mysql-bin.000009 > /tmp/mysql09.txt
[root@nbview db3306]# vim /tmp/mysql09.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#7001018:00:00 server id 71493306end_log_pos 120 CRC32 0x81e22bd2 Start: binlog v 4, server v 5.6.30-log created 7001018:00:00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
AAAAAA+65kIEdAAAAHgAAAABAAQANS42LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAdIr
4oE=
'/*!*/;
# at 120
#7001018:00:00 server id 71493306end_log_pos 151 CRC32 0x7ec5bbd1 Previous-GTIDs
# [empty]
2、复制bonlog方案二:
[root@nbview db3306]# mysqlbinlog -v 3306-mysql-bin.000009 --base64-output=decode-rows > /tmp/mysql20.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#7001018:00:00 server id 71493306end_log_pos 120 CRC32 0x81e22bd2 Start: binlog v 4, server v 5.6.30-log created 7001018:00:00
# Warning: this binlog is either in use or was not closed properly.
# at 120
#7001018:00:00 server id 71493306end_log_pos 151 CRC32 0x7ec5bbd1 Previous-GTIDs
# [empty]
# at 151
#160725 13:15:52 server id 71493306end_log_pos 199 CRC32 0x908b8e18 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '187588dc-4fb3-11e6-9a91-00163e0131f0:1'/*!*/;
# at 199
#160725 13:15:52 server id 71493306end_log_pos 314 CRC32 0x0016590a Query thread_id=31exec_time=0 error_code=0
SET TIMESTAMP=1469423752/*!*/;
SET @@session.pseudo_thread_id=31/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!/C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database student_db1
/*!*/;
# at 314(314表示下一个事务的position)
#160725 13:18:44 server id 71493306end_log_pos 362 CRC32 0x586d3310 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '187588dc-4fb3-11e6-9a91-00163e0131f0:2'/*!*/;
# at 362
#160725 13:18:44 server id 71493306end_log_pos 534 CRC32 0xdf055b3e Query thread_id=31exec_time=0 error_code=0
use `student_db1`/*!*/;
SET TIMESTAMP=1469423924/*!*/;
create table tab1(id int not null auto_increment,t_name varchar(32),primary key(id))
/*!*/;
# at 534
#160725 13:24:30 server id 71493306end_log_pos 582 CRC32 0xc86faa9a GTID [commit=yes]
##mysqlbinlog母版
mysqlbinlog -v --base64-output=decode-rows --stop-position=314 3310-mysql-bin.000003 > /tmp/mysql-bin.000003.txt##查看mysqlbinlog和mysqldump的帮助文档
[root@nbview db3306]# man mysqldump
[root@nbview db3306]# man mysqlbinlog##mysql_upgrade
mysql_upgrade -S /data1/db3310/my3310.sock
##数据库安全加固
mysql> select user,password,host from mysql.user;
+------+----------+------------+
| user | password | host|
+------+----------+------------+
| root | | localhost|
| root | | nbview.com |
| root | | 127.0.0.1|
| root | | ::1 |
|| | localhost|
|| | nbview.com |
+------+----------+------------+
6 rows in set (0.00 sec)
##创建新用户
mysql> create user 'root'@'%' identified by 'kewy126@home';
Query OK, 0 rows affected (0.00 sec)
##删除不需要的用户
mysql> select user,password,host from mysql.user;
+------+-------------------------------------------+------------+
| user | password| host|
+------+-------------------------------------------+------------+
| root | | localhost|
| root | | nbview.com |
| root | | 127.0.0.1|
| root | | ::1 |
|| | localhost|
|| | nbview.com |
| kewy | kewy126@home| % |
| root | *08F411191A8F7130F09F0A961DB8E87983620D5B | % |
+------+-------------------------------------------+------------+
8 rows in set (0.00 sec)
mysql> delete from mysql.user where user='kewy';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:59
Current database: student_db1
Query OK, 1 row affected (0.01 sec)
mysql> delete from mysql.user where user='root' and host='::1';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where user='root' and host='nbview.com';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where host='nbview.com';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where host='localhost' and user is null;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from mysql.user where host='127.0.0.1' ;
Query OK, 1 row affected (0.00 sec)
mysql> select user,password,host from mysql.user;
+------+-------------------------------------------+-----------+
| user | password| host|
+------+-------------------------------------------+-----------+
| root | | localhost |
| root | *08F411191A8F7130F09F0A961DB8E87983620D5B | %|
+------+-------------------------------------------+-----------+
2 rows in set (0.00 sec)
##授权
GRANT privileges ON databasename.tablename TO 'username'@'host';
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
##更改用户密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
##撤销用户权限
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
REVOKE SELECT ON *.* FROM 'pig'@'%';

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台