备份：
mysql的备份命令是mysqldump，mysql之备份： mysqldump [options] db_name [tbl_name ...] 恢复需要手动CRATE DATABASES mysqldump [options] --databases db_name ... 恢复不需要手动创建数据库 mysqldump [options] --all-databases 恢复不需要手动创建数据库 mysqldump -u user -h host --port -p [db[table...]...] >file -u：后面接数据库登录名 -h：界面接数据库服务器信息 --port：数据库的端口号 -p：数据库的密码，在语句执行后。会提示输入密码 file：备份文件将存放的路径 其他选项: -E, --events: 备份事件调度器 -R, --routines: 备份存储过程和存储函数 --triggers: 备份表的触发器; --skip-triggers --master-date[=value] 1: 记录为CHANGE MASTER TO 语句、语句不被注释 2: 记录为注释的CHANGE MASTER TO语句 基于二进制还原只能全库还原 --flush-logs: 日志滚动 锁定表完成后执行日志滚动 备份一个DB库中的table表 mysqldump -u ser -p db table> d:/201801.sql 备份多DB库 mysqldump -u ser -p --databases DB1 DB2 > d:/201802.sql --dabases后面可以跟多个库名，--all，可以备份所有库文件 mysqldump -u ser -p --all-databases> d:/201802.sql
恢复：
mysql的文件恢复有2种方法： 直接在mysql命令行执行sql语句操作导入 mysql -u user -p db < d:/2018.sql 通过source命令导入sql文件 use DB; source d:/2018.sql
查看状态position mysql -hlocalhost -uroot -p mysql> show master status -> ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 2912 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 备份 mysqldump -uroot -hlocalhost -p --all-databases --lock-all-tables > backup.sql 创建test2库，再次查看状态position mysql> CREATE DATABASE TEST2; Query OK, 1 row affected (0.12 sec) mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 3099 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 备份拷贝到备机 scp -P 10088 backup.sql 10.3.65.18:/root/
备机操作
mysql -hlocalhost -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ | TEST1 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> source backup.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)...... mysqlbinlog --start-position=2912 --stop-position=3099 mysql-bin.000007 | mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ | TEST1 | | TEST2 | | TEST3 | | information_schema | | mysql | | performance_schema | | sys | | testDB | +--------------------+ 8 rows in set (0.00 sec)
添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 不重启 [root@node1 ~]# ls /dev/sd* #只有以下几块硬盘, 但是我们不重启可以让系统识别新添加的硬盘 /dev/sda /dev/sda1 /dev/sda2 [root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host0/scan [root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host1/scan [root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host2/scan [root@node1 ~]# ls /dev/sd* #看！sdb识别出来了 /dev/sda /dev/sda1 /dev/sda2 /dev/sdb [root@node1 ~]# fdisk /dev/sdb #分区 Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel with disk identifier 0xd353d192. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) WARNING: DOS-compatible mode is deprecated. It's strongly recommended to switch off the mode (command 'c') and change display units to sectors (command 'u'). Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-2610, default 1): Using default value 1 Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +15G Command (m for help): t Selected partition 1 Hex code (type L to list codes): 8e Changed system type of partition 1 to 8e (Linux LVM) Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. You have new mail in /var/spool/mail/root [root@node1 ~]# partx -a /dev/sdb BLKPG: Device or resource busy error adding partition 1 ##创建逻辑卷 [root@node1 ~]# pvcreate /dev/sdb1 Physical volume "/dev/sdb1" successfully created [root@node1 ~]# vgcreate myvg /dev/sdb1 Volume group "myvg" successfully created [root@node1 ~]# lvcreate -n mydata -L 5G myvg Logical volume "mydata" created. [root@node1 ~]# mkfs.ext4 /dev/mapper/myvg-mydata #格式化 [root@node1 ~]# mkdir /lvm_data [root@node1 ~]# mount /dev/mapper/myvg-mydata /lvm_data #挂载到/lvm_data [root@node1 ~]# vim /etc/my.cnf #修改mysql配置文件的datadir如下 datadir=/lvm_data [root@node1 ~]# service mysqld restart #重启MySQL ####重新导入employees数据库########略过####
查看数据库的信息
mysql> SHOW DATABASES; #查看当前的数据库, 我们的数据库为employees +--------------------+ | Database | +--------------------+ | information_schema | | employees | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> USE employees; Database changed mysql> SHOW TABLES; #查看当前库中的表 +---------------------+ | Tables_in_employees | +---------------------+ | departments | | dept_emp | | dept_manager | | employees | | salaries | | titles | +---------------------+ 6 rows in set (0.00 sec) mysql> SELECT COUNT(*) FROM employees; #由于篇幅原因, 我们这里只看一下employees的行数为300024 +----------+ | COUNT(*) | +----------+ | 300024 | +----------+ 1 row in set (0.05 sec)
创建快照卷并备份
mysql> FLUSH TABLES WITH READ LOCK; #锁定所有表 Query OK, 0 rows affected (0.00 sec) [root@node1 lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata #创建快照卷 Logical volume "mydata-snap" created. mysql> UNLOCK TABLES; #解锁所有表 Query OK, 0 rows affected (0.00 sec) [root@node1 lvm_data]# mkdir /lvm_snap #创建文件夹 [root@node1 lvm_data]# mount /dev/myvg/mydata-snap /lvm_snap/ #挂载snap mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only [root@node1 lvm_data]# cd /lvm_snap/ [root@node1 lvm_snap]# ls employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test [root@node1 lvm_snap]# tar cf /tmp/mysqlback.tar * #打包文件到/tmp/mysqlback.tar [root@node1 ~]# umount /lvm_snap/ #卸载snap [root@node1 ~]# lvremove myvg mydata-snap #删除snap
恢复数据
[root@node1 lvm_snap]# rm -rf /lvm_data/* [root@node1 ~]# service mysqld start #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库 mysql> SHOW DATABASES; #查看数据库, 数据丢失! +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec) [root@node1 ~]# cd /lvm_data/ [root@node1 lvm_data]# rm -rf * #删除所有文件 [root@node1 lvm_data]# tar xf /tmp/mysqlback.tar #解压备份数据库到此文件夹 [root@node1 lvm_data]# ls #查看当前的文件 employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test mysql> SHOW DATABASES; #数据恢复了 +--------------------+ | Database | +--------------------+ | information_schema | | employees | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) ##完成