一、背景
公司的生产环境某些应用的数据库是部署在VMWare平台上的，为了节约成本，领导现考虑将全部数据迁移至居于KVM的CAS平台。之前做的是V2V迁移，但是效率不高，综合了一下时间成本及其他因素，最终选择采用备份与还原的方式进行迁移。源库中有GeekDevOps_gsms、GeekDevOps_list、GeekDevOps_ams等三个属于用户gsms的库。
二、环境
2.1备份主机环境
[root@GeekDevOps-DB1 ~]# hostnamectl Static hostname: GeekDevOps-DB1Icon name: computer-vmChassis: vmMachine ID: 18bc5992f24f495b84d8c231d63207eeBoot ID: f8f49625ca5748b8a1cdb9d64d6a547eVirtualization: vmwareOperating System: CentOS Linux 7 (Core)CPE OS Name: cpe:/o:centos:centos:7Kernel: Linux 3.10.0-327.el7.x86_64Architecture: x86-64
2.2还原主机环境
[root@GeekDevOps-DB2 ~]# hostnamectl Static hostname: GeekDevOps-DB2Icon name: computer-vmChassis: vmMachine ID: a4dbec2c1e1a496290d8f982bb758597Boot ID: 79abbf46968c475fabb1757b08c214aaVirtualization: kvmOperating System: CentOS Linux 7 (Core)CPE OS Name: cpe:/o:centos:centos:7Kernel: Linux 3.10.0-327.el7.x86_64Architecture: x86-64
三、备份过程
3.1停止数据库服务，杀死相关进程。
[root@GeekDevOps-DB1 ~]# systemctl stop mysqld[root@GeekDevOps-DB1 ~]# ps -ef |grep mysql
3.2备份。
[root@GeekDevOps-DB1 ~]# mysqldump -uroot -p GeekDevOps_gsms>GeekDevOps_gsms_bak_0323.sql[root@GeekDevOps-DB1 ~]# mysqldump -uroot -p GeekDevOps_list>GeekDevOps_list_bak_0323.sql[root@GeekDevOps-DB1 ~]# mysqldump -uroot -p GeekDevOps_ams>GeekDevOps_ams_bak_0323.sql
四、还原过程
4.1创建与源库相同的库。
[root@GeekDevOps-DB2 ~]# mysql -ugsms -pGeekDevOps,./mysql> CREATE DATABASE `GeekDevOps_gsms` DEFAULT CHARACTER SET utf8;Query OK, 1 row affected (0.01 sec)mysql> CREATE DATABASE `GeekDevOps_list` DEFAULT CHARACTER SET utf8;Query OK, 1 row affected (0.00 sec)mysql> CREATE DATABASE `GeekDevOps_ams` DEFAULT CHARACTER SET utf8;Query OK, 1 row affected (0.00 sec)
4.2为创建好的数据库授权给用户GeekDevOps_gsms。
mysql> grant all privileges on GeekDevOps_gsms.* to 'gsms'@'%' identified by "GeekDevOps,./";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> grant all privileges on GeekDevOps_list.* to 'gsms'@'%' identified by "GeekDevOps,./";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> grant all privileges on GeekDevOps_ams.* to 'gsms'@'%' identified by "GeekDevOps,./";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> grant all privileges on *.* to gsms@localhost identified by 'GeekDevOps,./' with grant option;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quitBye
4.3还原数据库。
[root@GeekDevOps-DB2 ~]# mysql -ugsms -pGeekDevOps,./ GeekDevOps_gsms<./GeekDevOps_gsms_bak_0323.sql mysql: [Warning] Using a password on the command line interface can be insecure.[root@GeekDevOps-DB2 ~]# mysql -ugsms -pGeekDevOps,./ GeekDevOps_list<./GeekDevOps_list_bak_0323.sql mysql: [Warning] Using a password on the command line interface can be insecure.[root@GeekDevOps-DB2 ~]# mysql -ugsms -pGeekDevOps,./ GeekDevOps_ams<./GeekDevOps_ams1_bak_0323.sql mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use GeekDevOps_gsms;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -Amysql> quitBye
五、检查数据完整性
[root@GeekDevOps-DB2 ~]# mysql -u root -p -AEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.21 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use GeekDevOps_gsms;Database changedmysql> show tables;mysql> use GeekDevOps_list;Database changedmysql> show tables;mysql> use GeekDevOps_ams;Database changedmysql> show tables;mysql> select User,Db from Db;+---------------+---------------------------+| User| Db |+---------------+---------------------------+| gsms| GeekDevOps_ams || gsms| GeekDevOps_gsms || gsms| GeekDevOps_list || mysql.session | performance_schema || mysql.sys| sys |+---------------+---------------------------+5 rows in set (0.00 sec)
注意事项：本文中的数据备份仅仅备份了表内数据，诸如函数、存储过程等内容均未进行备份。备份的各个选项见下：
mysqldump Ver 10.13 Distrib 5.7.21, for Linux (x86_64)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Dumping structure and contents of MySQL databases and tables.Usage: mysqldump [OPTIONS] database [tables]ORmysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]ORmysqldump [OPTIONS] --all-databases [OPTIONS]Default options are read from the following files in the given order:/etc/f /etc/mysql/f /usr/etc/f ~/.f The following groups are read: mysqldump clientThe following options may be given as the first argument:--print-defaults Print the program argument list and exit.--no-defaults Don't read default options from any option file,except for login file.--defaults-file=# Only read default options from the given file #.--defaults-extra-file=# Read this file after the global files are read.--defaults-group-suffix=#Also read groups with concat(group, suffix)--login-path=#Read this path from the login file.-A, --all-databases Dump all the databases. This will be same as --databaseswith all databases selected.-Y, --all-tablespaces Dump all the tablespaces.-y, --no-tablespaces Do not dump any tablespace information.--add-drop-database Add a DROP DATABASE before each create.--add-drop-table Add a DROP TABLE before each create.(Defaults to on; use --skip-add-drop-table to disable.)--add-drop-trigger Add a DROP TRIGGER before each create.--add-locks Add locks around INSERT statements.(Defaults to on; use --skip-add-locks to disable.)--allow-keywords Allow creation of column names that are keywords.--apply-slave-statements Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'STARTSLAVE' to bottom of dump.--bind-address=name IP address to bind to.--character-sets-dir=name Directory for character set files.-i, --commentsWrite additional information.(Defaults to on; use --skip-comments to disable.)--compatible=name Change the dump to be compatible with a given mode. Bydefault tables are dumped in a format optimized forMySQL. Legal modes are: ansi, mysql323, mysql40,postgresql, oracle, mssql, db2, maxdb, no_key_options,no_table_options, no_field_options. One can use severalmodes separated by commas. Note: Requires MySQL serverversion 4.1.0 or higher. This option is ignored withearlier server versions.--compact Give less verbose output (useful for debugging). Disablesstructure comments and header/footer constructs. Enablesoptions --skip-add-drop-table --skip-add-locks--skip-comments --skip-disable-keys --skip-set-charset.-c, --complete-insert Use complete insert statements.-C, --compressUse compression in server/client protocol.-a, --create-options Include all MySQL specific create options.(Defaults to on; use --skip-create-options to disable.)-B, --databasesDump several databases. Note the difference in usage; inthis case no tables are given. All name arguments areregarded as database names. 'USE db_name;' will beincluded in the output.-#, --debug[=#]This is a non-debug version. Catch this and exit.--debug-check This is a non-debug version. Catch this and exit.--debug-info This is a non-debug version. Catch this and exit.--default-character-set=name Set the default character set.--delete-master-logs Delete logs on master after backup. This automaticallyenables --master-data.-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be putin the output.(Defaults to on; use --skip-disable-keys to disable.)--dump-slave[=#] This causes the binary log position and filename of themaster to be appended to the dumped data output. Settingthe value to 1, will printit as a CHANGE MASTER commandin the dumped data output; if equal to 2, that commandwill be prefixed with a comment symbol. This option willturn --lock-all-tables on, unless --single-transaction isspecified too (in which case a global read lock is onlytaken a short time at the beginning of the dump - don'tforget to read about --single-transaction below). In allcases any action on logs will happen at the exact momentof the dump.Option automatically turns --lock-tables off.-E, --events Dump events.-e, --extended-insert Use multiple-row INSERT syntax that include severalVALUES lists.(Defaults to on; use --skip-extended-insert to disable.)--fields-terminated-by=name Fields in the output file are terminated by the givenstring.--fields-enclosed-by=name Fields in the output file are enclosed by the givencharacter.--fields-optionally-enclosed-by=name Fields in the output file are optionally enclosed by thegiven character.--fields-escaped-by=name Fields in the output file are escaped by the givencharacter.-F, --flush-logs Flush logs file in server before starting dump. Note thatif you dump many databases at once (using the option--databases= or --all-databases), the logs will beflushed for each database dumped. The exception is whenusing --lock-all-tables or --master-data: in this casethe logs will be flushed only once, corresponding to themoment all tables are locked. So if you want your dumpand the log flush to happen at the same exact moment youshould use --lock-all-tables or --master-data with--flush-logs.--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysqldatabase. This option should be used any time the dumpcontains the mysql database and any other database thatdepends on the data in the mysql database for properrestore. -f, --force Continue even if we get an SQL error.-?, --helpDisplay this help message and exit.--hex-blobDump binary strings (BINARY, VARBINARY, BLOB) inhexadecimal format.-h, --host=nameConnect to host.--ignore-error=name A comma-separated list of error numbers to be ignored ifencountered during dump.--ignore-table=name Do not dump the specified table. To specify more than onetable to ignore, use the directive multiple times, oncefor each table. Each table must be specified with bothdatabase and table names, e.g.,--ignore-table=database.table.--include-master-host-port Adds 'MASTER_HOST=<host>, MASTER_PORT=<port>' to 'CHANGEMASTER TO..' in dump produced with --dump-slave.--insert-ignoreInsert rows with INSERT IGNORE.--lines-terminated-by=name Lines in the output file are terminated by the givenstring.-x, --lock-all-tables Locks all tables across all databases. This is achievedby taking a global read lock for the duration of thewhole dump. Automatically turns --single-transaction and--lock-tables off.-l, --lock-tables Lock all tables for read.(Defaults to on; use --skip-lock-tables to disable.)--log-error=name Append warnings and errors to given file.--master-data[=#] This causes the binary log position and filename to beappended to the output. If equal to 1, will print it as aCHANGE MASTER command; if equal to 2, that command willbe prefixed with a comment symbol. This option will turn--lock-all-tables on, unless --single-transaction isspecified too (in which case a global read lock is onlytaken a short time at the beginning of the dump; don'tforget to read about --single-transaction below). In allcases, any action on logs will happen at the exact momentof the dump. Option automatically turns --lock-tablesoff.--max-allowed-packet=# The maximum packet length to send to or receive fromserver.--net-buffer-length=# The buffer size for TCP/IP and socket communication.--no-autocommitWrap tables with autocommit/commit statements.-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement thatnormally is output for each dumped database if--all-databases or --databases is given.-t, --no-create-info Don't write table creation info.-d, --no-data No row information.-N, --no-set-names Same as --skip-set-charset.--optSame as --add-drop-table, --add-locks, --create-options,--quick, --extended-insert, --lock-tables, --set-charset,and --disable-keys. Enabled by default, disable with--skip-opt.--order-by-primary Sorts each table's rows by primary key, or first uniquekey, if such a key exists. Useful when dumping a MyISAMtable to be loaded into an InnoDB table, but will makethe dump itself take considerably longer.-p, --password[=name] Password to use when connecting to server. If password isnot given it's solicited on the tty.-P, --port=# Port number to use for connection.--protocol=nameThe protocol to use for connection (tcp, socket, pipe,memory).-q, --quick Don't buffer query, dump directly to stdout.(Defaults to on; use --skip-quick to disable.)-Q, --quote-names Quote table and column names with backticks (`).(Defaults to on; use --skip-quote-names to disable.)--replace Use REPLACE INTO instead of INSERT INTO.-r, --result-file=name Direct output to a given file. This option should be usedin systems (e.g., DOS, Windows) that use carriage-returnlinefeed pairs (\r\n) to separate text lines. This optionensures that only a single newline is used.-R, --routinesDump stored routines (functions and procedures).--set-charset Add 'SET NAMES default_character_set' to the output.(Defaults to on; use --skip-set-charset to disable.)--set-gtid-purged[=name] Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possiblevalues for this option are ON, OFF and AUTO. If ON isused and GTIDs are not enabled on the server, an error isgenerated. If OFF is used, this option does nothing. IfAUTO is used and GTIDs are enabled on the server, 'SET@@GLOBAL.GTID_PURGED' is added to the output. If GTIDsare disabled, AUTO does nothing. If no value is suppliedthen the default (AUTO) value will be considered.--single-transaction Creates a consistent snapshot by dumping all tables in asingle transaction. Works ONLY for tables stored instorage engines which support multiversioning (currentlyonly InnoDB does); the dump is NOT guaranteed to beconsistent for other storage engines. While a--single-transaction dump is in process, to ensure avalid dump file (correct table contents and binary logposition), no other connection should use the followingstatements: ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE, as consistent snapshot is not isolatedfrom them. Option automatically turns off --lock-tables.--dump-date Put a dump date to the end of the output.(Defaults to on; use --skip-dump-date to disable.)--skip-optDisable --opt. Disables --add-drop-table, --add-locks,--create-options, --quick, --extended-insert,--lock-tables, --set-charset, and --disable-keys.-S, --socket=name The socket file to use for connection.--secure-auth Refuse client connecting to server if it uses old(pre-4.1.1) protocol. Deprecated. Always TRUE--ssl-mode=nameSSL connection mode.--sslDeprecated. Use --ssl-mode instead.(Defaults to on; use --skip-ssl to disable.)--ssl-verify-server-cert Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.--ssl-ca=name CA file in PEM format.--ssl-capath=name CA directory.--ssl-cert=nameX509 cert in PEM format.--ssl-cipher=name SSL cipher to use.--ssl-key=nameX509 key in PEM format.--ssl-crl=nameCertificate revocation list.--ssl-crlpath=name Certificate revocation list path.--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1-T, --tab=nameCreate tab-separated textfile for each table to givenpath. (Create .sql and .txt files.) NOTE: This only worksif mysqldump is run on the same machine as the mysqldserver.--tables Overrides option --databases (-B).--triggersDump triggers for each dumped table.(Defaults to on; use --skip-triggers to disable.)--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping ofTIMESTAMP data when a server has data in different timezones or data is being moved between servers withdifferent time zones.(Defaults to on; use --skip-tz-utc to disable.)-u, --user=nameUser for login if not current user.-v, --verbose Print info about the various stages.-V, --version Output version information and exit.-w, --where=name Dump only selected records. Quotes are mandatory.-X, --xml Dump a database as well formed XML.--plugin-dir=name Directory for client-side plugins.--default-auth=name Default authentication client-side plugin to use.--enable-cleartext-plugin Enable/disable the clear text authentication plugin.Variables (--variable-name=value)and boolean options {FALSE|TRUE} Value (after reading options)--------------------------------- ----------------------------------------all-databases FALSEall-tablespaces FALSEno-tablespacesFALSEadd-drop-database FALSEadd-drop-tableTRUEadd-drop-trigger FALSEadd-locksTRUEallow-keywordsFALSEapply-slave-statements FALSEbind-address (No default value)character-sets-dir(No default value)commentsTRUEcompatible (No default value)compact FALSEcomplete-insert FALSEcompressFALSEcreate-optionsTRUEdatabasesFALSEdefault-character-set utf8delete-master-logsFALSEdisable-keys TRUEdump-slave 0events FALSEextended-insert TRUEfields-terminated-by (No default value)fields-enclosed-by(No default value)fields-optionally-enclosed-by(No default value)fields-escaped-by (No default value)flush-logs FALSEflush-privileges FALSEforce FALSEhex-blobFALSEhost(No default value)ignore-error (No default value)include-master-host-portFALSEinsert-ignore FALSElines-terminated-by(No default value)lock-all-tables FALSElock-tables TRUElog-error(No default value)master-data 0max-allowed-packet25165824net-buffer-length 1046528no-autocommit FALSEno-create-db FALSEno-create-infoFALSEno-data FALSEorder-by-primary FALSEport0quick TRUEquote-names TRUEreplace FALSEroutinesFALSEset-charset TRUEsingle-transactionFALSEdump-dateTRUEsocket (No default value)secure-auth TRUEssl TRUEssl-verify-server-cert FALSEssl-ca (No default value)ssl-capath (No default value)ssl-cert(No default value)ssl-cipher (No default value)ssl-key (No default value)ssl-crl (No default value)ssl-crlpath (No default value)tls-version (No default value)tab (No default value)triggersTRUEtz-utc TRUEuser(No default value)verbose FALSEwhere (No default value)plugin-dir (No default value)default-auth (No default value)enable-cleartext-plugin FALSE
如果觉得《记一次生产环境MySQL数据库的备份与还原》对你有帮助，请点赞、收藏，并留下你的观点哦！
