insert data
innodb
[root@test ~]# /service/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.25-log zhongwc_DB
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use zwc
Database changed
mysql> DELIMITER ;;
mysql> CREATE PROCEDURE proc_buildata(IN total INT)
-> BEGIN
-> DECLARE var INT DEFAULT 0;
-> PREPARE DDL_SQL FROM 'CREATE TABLE `employee` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`employeeid` int(10) unsigned NOT NULL,`employeename` varchar(64) NOT NULL DEFAULT '''',PRIMARY KEY (`id`)) ENGINE=InnoDB';
-> EXECUTE DDL_SQL;
-> WHILE var<total DO
-> SET var=var+1;
-> INSERT INTO employee (employeeid,employeename) VALUES (var,CONCAT('test',var));
-> END WHILE;
-> END ;;
Query OK, 0 rows affected (0.06 sec)
mysql> call proc_buildata(1000000);
Query OK, 1 row affected (1 min 5.79 sec)
mysql> select count(*) zwc.employee;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.employee' at line 1
mysql> select count(*) from zwc.employee;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.17 sec)
mysql> show create table zwc.employee\G
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`employeeid` int(10) unsigned NOT NULL,
`employeename` varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)
myisam
mysql> create table zwc.employee_myisam engine = myisam as select * from zwc.employee;
Query OK, 1000000 rows affected (0.90 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> show create table zwc.employee_myisam\G
*************************** 1. row ***************************
Table: employee_myisam
Create Table: CREATE TABLE `employee_myisam` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`employeeid` int(10) unsigned NOT NULL,
`employeename` varchar(64) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select count(*) from zwc.employee_myisam;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.00 sec)
datafile,. ibd for innodb datafile
[root@test ~]# ls /data/mysql/zwc/ -lh total 72M -rw-rw---- 1 mysql mysql 61 Jan 10 11:20 db.opt -rw-rw---- 1 mysql mysql 8.5K Jan 10 11:27 employee.frm -rw-rw---- 1 mysql mysql 48M Jan 10 11:28 employee.ibd -rw-rw---- 1 mysql mysql 8.5K Jan 10 11:30 employee_myisam.frm -rw-rw---- 1 mysql mysql 23M Jan 10 11:30 employee_myisam.MYD -rw-rw---- 1 mysql mysql 1.0K Jan 10 11:30 employee_myisam.MYI
create backup recovery users
mysql> GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost' IDENTIFIED BY 'bakuser'; Query OK, 0 rows affected (0.03 sec)
run full backup
[root@test mysql]# /service/mysql/bin/innobackupex --user=bakuser --password=bakuser --port=30306 --slave-info --tmpdir=/backup/mysql --defaults-file=/service/mysql/my.cnf /backup/mysql
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2011. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
130110 14:33:36 innobackupex: Starting mysql with options: --defaults-file='/service/mysql/my.cnf' --password='bakuser' --user='bakuser' --port='30306' --unbuffered --
130110 14:33:36 innobackupex: Connected to database with mysql child process (pid=13503)
130110 14:33:42 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using mysql Ver 14.14 Distrib 5.5.25, for Linux (x86_64) using readline 5.1
innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
innobackupex: Created backup directory /backup/mysql/2013-01-10_14-33-42
130110 14:33:42 innobackupex: Starting mysql with options: --defaults-file='/service/mysql/my.cnf' --password='bakuser' --user='bakuser' --port='30306' --unbuffered --
130110 14:33:42 innobackupex: Connected to database with mysql child process (pid=13530)
130110 14:33:46 innobackupex: Connection to database server closed
130110 14:33:46 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/service/mysql/my.cnf" --backup --suspend-at-end --target-dir=/backup/mysql/2013-01-10_14-33-42
innobackupex: Waiting for ibbackup (pid=13541) to suspend
innobackupex: Suspend file '/backup/mysql/2013-01-10_14-33-42/xtrabackup_suspended'
xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
130110 14:33:46 InnoDB: Using Linux native AIO
xtrabackup: use O_DIRECT
130110 14:33:47 InnoDB: Warning: allocated tablespace 2, old maximum was 0
>> log scanned up to (297286751)
[01] Copying ./ibdata1
to /backup/mysql/2013-01-10_14-33-42/ibdata1
[01] ...done
[01] Copying ./zwc/employee.ibd
to /backup/mysql/2013-01-10_14-33-42/zwc/employee.ibd
[01] ...done
130110 14:33:50 innobackupex: Continuing after ibbackup has suspended
130110 14:33:50 innobackupex: Starting mysql with options: --defaults-file='/service/mysql/my.cnf' --password='bakuser' --user='bakuser' --port='30306' --unbuffered --
130110 14:33:50 innobackupex: Connected to database with mysql child process (pid=13556)
>> log scanned up to (297286751)
130110 14:33:54 innobackupex: Starting to lock all tables...
>> log scanned up to (297286751)
>> log scanned up to (297286751)
130110 14:34:06 innobackupex: All tables locked and flushed to disk
130110 14:34:06 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/data/mysql'
innobackupex: Backing up file '/data/mysql/zwc/employee_myisam.MYD'
innobackupex: Backing up file '/data/mysql/zwc/employee_myisam.frm'
innobackupex: Backing up file '/data/mysql/zwc/employee_myisam.MYI'
innobackupex: Backing up file '/data/mysql/zwc/db.opt'
innobackupex: Backing up file '/data/mysql/zwc/employee.frm'
innobackupex: Backing up files '/data/mysql/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up files '/data/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
130110 14:34:07 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files
innobackupex: Resuming ibbackup
xtrabackup: The latest check point (for incremental): '297286751'
>> log scanned up to (297286751)
>> log scanned up to (297286751)
xtrabackup: Stopping log copying thread..
xtrabackup: Transaction log of lsn (297286751) to (297286751) was copied.
130110 14:34:09 innobackupex: All tables unlocked
130110 14:34:09 innobackupex: Connection to database server closed
innobackupex: Backup created in directory '/backup/mysql/2013-01-10_14-33-42'
innobackupex: MySQL binlog position: filename 'binlog-master.000004', position 305779142
innobackupex: MySQL slave binlog position: master host '', filename '', position
130110 14:34:09 innobackupex: completed OK!
Note:
In the backup process, the INNODB data file backup is completed, will lock the database, and began to copy MYISAM and non affairs engine data and .frm. So if you have more MYISAM table, lock database
will last very long. If it is in the master database to run on, must pay attention to.
full recovery
apply log
This process is mainly produce REDOLOG and backup produced during REDO applied to data file, xtrabackup will start a INNODB process to do it, and your current in running don't conflict.
[root@test ~]# rm -rf /data/mysql/zwc
[root@test ~]# /service/mysql/bin/innobackupex --user=bakuser --password=bakuser --defaults-file=/service/mysql/my.cnf --apply-log /backup/mysql/2013-01-10_14-33-42/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2011. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
130110 14:47:06 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/service/mysql/my.cnf" --prepare --target-dir=/backup/mysql/2013-01-10_14-33-42
xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /backup/mysql/2013-01-10_14-33-42
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(297286751)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
130110 14:47:06 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
130110 14:47:06 InnoDB: The InnoDB memory heap is disabled
130110 14:47:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130110 14:47:06 InnoDB: Compressed tables use zlib 1.2.3
130110 14:47:06 InnoDB: Using Linux native AIO
130110 14:47:06 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
130110 14:47:06 InnoDB: Initializing buffer pool, size = 100.0M
130110 14:47:06 InnoDB: Completed initialization of buffer pool
130110 14:47:06 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130110 14:47:06 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 305778818, file name ./binlog-master.000004
130110 14:47:06 InnoDB: Error: table 'zwc/t_test'
InnoDB: in InnoDB data dictionary has tablespace id 1,
InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.
130110 14:47:06 InnoDB: Waiting for the background threads to start
130110 14:47:07 InnoDB: 1.1.5 started; log sequence number 297286751
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 305778818, file name ./binlog-master.000004
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130110 14:47:07 InnoDB: Starting shutdown...
130110 14:47:08 InnoDB: Shutdown completed; log sequence number 297288039
130110 14:47:08 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/service/mysql/my.cnf" --prepare --target-dir=/backup/mysql/2013-01-10_14-33-42
for creating ib_logfile*
xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /backup/mysql/2013-01-10_14-33-42
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
130110 14:47:08 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
130110 14:47:08 InnoDB: The InnoDB memory heap is disabled
130110 14:47:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130110 14:47:08 InnoDB: Compressed tables use zlib 1.2.3
130110 14:47:08 InnoDB: Using Linux native AIO
130110 14:47:08 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
130110 14:47:08 InnoDB: Initializing buffer pool, size = 100.0M
130110 14:47:08 InnoDB: Completed initialization of buffer pool
130110 14:47:08 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
130110 14:47:11 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
130110 14:47:14 InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
130110 14:47:17 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130110 14:47:17 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 305778818, file name ./binlog-master.000004
130110 14:47:18 InnoDB: Waiting for the background threads to start
130110 14:47:19 InnoDB: 1.1.5 started; log sequence number 297288204
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 305778818, file name ./binlog-master.000004
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130110 14:47:19 InnoDB: Starting shutdown...
130110 14:47:19 InnoDB: Shutdown completed; log sequence number 297288204
130110 14:47:19 innobackupex: completed OK!
copy the data files to data directory
This process will restore good data copy to my. cnf specified in the data directory. Then you need to put the original case stop.
[root@test ~]# /service/mysql/bin/mysqladmin -uroot shutdown -p Enter password: [root@test ~]# ps -ef|grep mysql |grep -v grep [root@test ~]#
[root@test opt]# /service/mysql/bin/innobackupex --user=bakuser --password=bakuser --defaults-file=/service/mysql/my.cnf --copy-back /backup/mysql/2013-01-10_14-33-42/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2011. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
sh: autodetect: command not found
innobackupex: fatal error: no 'mysqld' group in MySQL options
innobackupex: fatal error: OR no 'datadir' option in group 'mysqld' in MySQL options
Estimate here is the 1.62 version of the bug.There is no way, manual copying datafile.
[root@test ~]# cd /backup/mysql/2013-01-10_14-33-42/ [root@test 2013-01-10_14-33-42]# cp -rp zwc/ /data/mysql/ [root@test 2013-01-10_14-33-42]# chown -R mysql.mysql /data/mysql/zwc
startup mysql validation recovery
[root@test 2013-01-10_14-33-42]# /service/mysql/bin/mysqld_safe --defaults-file=/service/mysql/my.cnf --basedir=/service/mysql --datadir=/data/mysql --user=mysql & [1] 14663 [root@test 2013-01-10_14-33-42]# 130110 15:05:01 mysqld_safe Logging to '/data/mysql/test.localdomain.err'. 130110 15:05:01 mysqld_safe Starting mysqld daemon with databases from /data/mysql [root@test 2013-01-10_14-33-42]# /service/mysql/bin/mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.25-log zhongwc_DB Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use zwc Database changed mysql> select count(*) from employee; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.60 sec) mysql> select count(*) from employee_myisam; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.01 sec)