MySQL backup mysqldump
Note: Backup files and binary log files cannot be placed on the same disk as MySQL
Node 1
1, modify the MySQL configuration file on Node 1, open binary log Save
Here I put the binary log in the/data/mysql/directory,/data/is the other LVM disk I created, I would like to put in the/data/, I found that I could not start MySQL, so it is recommended to put in the/data/mysql
[[email protected] ~]# mkdir-pv/data/mysql/[[email protected] ~]# chown mysql:mysql/data/*[[email protected] mysql]# CD /var/lib/mysql[[email protected] mysql]# cp-a mysql-bin.000001 mysql-bin.000002 mysql-bin.index/data/mysql/[[email Protected] ~]# vim/etc/my.cnf.d/server.cnf [Server] log_bin=/data/mysql/mysql-bin[[email protected] ~]# service Mar IADB restart
2. Check the binary log for some information
[[email protected] ~]# MySQL MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 264 | | mysql-bin.000002 | 245 | +------------------+-----------+
3. View the storage engine type of the table and back up
MariaDB [hellodb]> Show Table status\g;
If the engine is MyISAM then the backup scheme is as follows and requires operation on the lock table
[Email protected] ~]# mysqldump-uroot--lock-tables--master-data=2--flush-logs--databases hellodb >/root/hellodb _myis.sql
If the engine is InnoDB then the backup scheme is as follows
[Email protected] ~]# mysqldump-uroot--single-transaction--master-data=2--flush-logs--databases hellodb >/root/ Hellodb_inno.sql--single-transaction: Hot standby--master-data=2: Change master to statement recorded as comment--flush-logs: Log scrolling
Bulk Modify table Storage Engine "will get the results of a single execution can be modified, not recommended to modify directly in MySQL"
MariaDB [hellodb]> SELECT CONCAT (' ALTER TABLE ', table_name, ' engine=innodb; ') From Information_schema.tables WHERE table_schema= ' hellodb ' and engine= ' MyISAM ';
4. Modify the data in the table
MariaDB [(None)]> use Hellodb; MariaDB [hellodb]> INSERT INTO students (Name,age,gender,classid,teacherid) VALUES (' Caocao ', ", ' M ', 6,8); MariaDB [hellodb]> Delete from students where stuid=3;
5. Copy the backup file to another node
[Email protected] ~]# SCP hellodb_inno.sql 192.168.1.114:/root/
Node 2
6. mysql Recovery on another node
Modify the configuration file for node 2
[Email protected] ~]# mkdir-pv/data/mysql[[email protected] ~]# vim/etc/my.cnf [mysqld] Log_bin=/data/mysql/mys Ql-bin[[email protected] ~]# chown mysql:mysql/data/*[[email protected] ~]# chown mysql:mysql/data[[email protected] ~]# Service MARIADB Start
Restore Backup Files
[[email protected] ~]# MySQL </root/hellodb_inno.sql[[email protected] ~]# less hellodb_inno.sql – change MASTER T O master_log_file= ' mysql-bin.000002 ', master_log_pos=245;
According to the display in the table, at the moment of backup, the binary log mysql-bin.000002, operation to 245
7. Restore the binary Log on Node 2
Convert a binary log file after 245 to a SQL file on Node 1
[Email protected] ~]# Mysqlbinlog--start-position=245/var/lib/mysql/mysql-bin.000002 > Binlog.sql
Copy to Node 2
[Email protected] ~]# SCP binlog.sql 192.168.1.114:/root/
Use the SQL file you just produced to recover the contents of the operation after the backup
[[email protected] ~]# MySQL </root/binlog.sql
8. View recovery Status
[[Email protected] ~]# mysqlmariadb [(None)]> use hellodb; mariadb [hellodb]> select * from students;+-------+---------------+-----+--------+ ---------+-----------+| stuid | name | age | gender | classid | teacherid |+-------+---------------+ + ----+--------+---------+-----------+| 1 | shi zhongyu | 22 | M | 2 | 3 | | 2 | shi potian | 22 | M | 1 | 7 | | 4 | ding dian | 32 | m | 4 | 4 | | 5 | yu yutong | 26 | M | 3 | 1 | | 6 | shi qing | 46 | M | 5 | null | | 7 | Xi Ren | 19 | f | 3 | null | | 8 | lin daiyu | 17 | F | 7 | null | | 9 | Ren Yingying | 20 | F | 6 | null | | 10 | Yue Lingshan | 19 | F | 3 | null | | 11 | Yuan Chengzhi | 23 | M | 6 | null | | 12 | wen qingqing | 19 | f | 1 | null | | 13 | Tian Boguang | 33 | M | 2 | null | | 14 | Lu Wushuang | 17 | F | 3 | null | | 15 | duan yu | 19 | M | 4 | null | | 16 | xu zhu | 21 | m | 1 | null | | 17 | lin chong | 25 | M | 4 | null | | 18 | hua rong | 23 | M | 7 | null | | 19 | Xue Baochai | 18 | F | 6 | null | | 20 | diao chan | 19 | f | 7 | null | | 21 | Huang Yueying | 22 | F | 6 | null | | 22 | xiao qiao | 20 | F | 1 | null | | 23 | ma chao | 23 | M | 4 | null | | 24 | xu xian | 27 | m | null | null | | 25 | Sun Dasheng | 100 | M | null | null | | 26 | caocao | 99 | M | 6 | 8 |+-------+---------------+-----+--------+---------+---- -------+
This article is from the "Wind" blog, please be sure to keep this source http://xsllqs.blog.51cto.com/2308669/1827605
MySQL backup mysqldump