所以备份是重要的,但光有备份还不行,备份后如果出现问题,你还得使用备份数据来恢复,但恢复数据的时间一般都是很长的,不符合业务需求,所以一个快速备份与恢复的软件就很有必要。 之前我在维护mysql数据库的时候,使用mysqldump来进行备份与恢复,在备份的时候锁住表,然后全部备份,在数据少的时候没问题,但如果数据很多,不允许锁表,同时需要恢复数据块的情况,mysqldump就不适合了,我在恢复一个4G数据文件的数据库的时候,恢复的数据是使用mysqldump的数据,恢复了3个小时还没有反应,造成的影响很严重,所以我开始寻找其他的别发软件来满足以上的需求,幸好找到了,就是使用xtrabackup来进行备份与恢复,恢复4G数据文件的数据库,仅需要14秒,同时在备份的时候不会锁表,而且支持增量备份,所以把我的比较分享给大家,希望对大家有益! Xtrabackup 是percona公司的开源项目,用以实现类似innodb官方的热备份工具InnoDB Hot Backup的功能,能够非常快速地备份与恢复mysql数据库。 Xtrabackup中包含两个工具: xtrabackup是用于热备份innodb, xtradb表中数据的工具,不能备份其他类型的表,也不能备份数据表结构; innobackupex是将xtrabackup进行封装的perl脚本,提供了备份myisam表的能力。 由于innobackupex的功能更为全面和完善,所以,本文以innobackupex作为基础进行研究描述。 下面介绍xtrabackup的全部、增量的备份与恢复。 一、下载与安装 1、下载 wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6.7/binary/Linux/x86_64/xtrabackup-1.6.7.tar.gz 2、安装依赖库 如果是debian系列的话 apt-get install debhelper autotools-dev libaio-dev wget automake libtool bison libncurses-dev libz-dev cmake bzr 如果是redhat系列的话 yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel 3、解压 tar zxvf xtrabackup-1.6.7.tar.gz 4、进入目录 cd xtrabackup-1.6.7 5、复制 cd bin cp * /usr/bin 然后就安装完成了,下面开始备份 其中, innobackupex是我们要使用的备份工具; xtrabackup是被封装在innobackupex之中的,innobackupex运行时需要调用它; xtrabackup_51是xtrabackup运行时需要调用的工具; tar4ibd是以tar流的形式产生备份时用来打包的工具。 6、对某个数据库进行全部备份的命令介绍 innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --database=test --stream=tar /tmp/data/ 2>/tmp/data/err.log|gzip 1>/tmp/data/test.tar.gz 说明: --database=test 单独对test数据库做备份 ,若是不添加此参数那就那就是对全库做备份 2>/tmp/data/err.log 输出信息写入日志中 1>/tmp/data/test.tar.gz 打包压缩存储到该文件中 二、对数据库的全部备份与恢复 下面开始测试xtrabackup的全部备份 (1)先进入mysql里创建一个新的test数据库 root@client2:/tmp# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 40 Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) Copyright (c) 2000, 2012, 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> drop database test; Query OK, 3 rows affected (0.13 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> create table test (id int); Query OK, 0 rows affected (0.06 sec) mysql> insert into test values(1); Query OK, 1 row affected (0.04 sec) mysql> insert into test values(2); Query OK, 1 row affected (0.01 sec) mysql> insert into test values(3); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(4); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(5); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) (2)然后备份test的整个数据库 使用下面的backup.sh脚本 root@client2:/tmp# cat backup.sh #!/bin/bash user='root' passwd='123456' database=test my_config='/etc/mysql/my.cnf' log=$database-$(date +%Y%m%d%H%M).log str=$database-$(date +%Y%m%d%H%M).tar.gz backup_dir='/tmp/data' echo "Start to backup at $(date +%Y%m%d%H%M)" if [ ! -d "$backup_dir" ];then mkdir $backup_dir fi innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database --stream=tar $backup_dir 2>$backup_dir/$log | gzip 1>$backup_dir/$str if [ $? -eq 0 ];then echo "Backup is finish! at $(date +%Y%m%d%H%M)" exit 0 else echo "Backup is Fail! at $(date +%Y%m%d%H%M)" exit 1 fi 现在开始运行此脚本 root@client2:/tmp# sh backup.sh Start to backup at 201303072101 Backup is finish! at 201303072102
然后到data里查看结果
root@client2:/tmp# cd data root@client2:/tmp/data# ll total 3272 drwxr-xr-x 2 root root 4096 Mar 7 21:01 ./ drwxrwxrwt 13 root root 4096 Mar 7 21:02 ../ -rw-r--r-- 1 root root 3780 Mar 7 21:02 test-201303072101.log -rw-r--r-- 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz root@client2:/tmp/data# cat test-201303072101.log InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Inc 2009-2012. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 130307 21:01:39 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 130307 21:01:39 innobackupex: Connected to database with mysql child process (pid=12441) 130307 21:01:45 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.28, for debian-linux-gnu (x86_64) using readline 6.2 innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. innobackupex: Created backup directory /tmp/data 130307 21:01:45 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 130307 21:01:45 innobackupex: Connected to database with mysql child process (pid=12471) 130307 21:01:47 innobackupex: Connection to database server closed 130307 21:01:47 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/mysql/my.cnf" --backup --suspend-at-end --log-stream --target-dir=/tmp innobackupex: Waiting for ibbackup (pid=12478) to suspend innobackupex: Suspend file '/tmp/xtrabackup_suspended' xtrabackup: suspend-at-end is enabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/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 = 2 xtrabackup: innodb_log_file_size = 5242880 130307 21:01:47 InnoDB: Using Linux native AIO xtrabackup: Stream mode. >> log scanned up to (59605543) 130307 21:01:49 innobackupex: Continuing after ibbackup has suspended innobackupex: Starting to backup InnoDB tables and indexes innobackupex: from original InnoDB data directory '/var/lib/mysql' innobackupex: Backing up as tar stream 'ibdata1' 130307 21:01:52 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 130307 21:01:52 innobackupex: Connected to database with mysql child process (pid=12494) >> log scanned up to (59605543) 130307 21:01:54 innobackupex: Starting to lock all tables... >> log scanned up to (59605543) >> log scanned up to (59605543) 130307 21:02:04 innobackupex: All tables locked and flushed to disk 130307 21:02:04 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in innobackupex: subdirectories of '/var/lib/mysql' innobackupex: Backing up file '/var/lib/mysql/test/test.frm' innobackupex: Backing up file '/var/lib/mysql/test/db.opt' 130307 21:02:04 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): '59605543' >> log scanned up to (59605543) xtrabackup: Transaction log of lsn (59605543) to (59605543) was copied. 130307 21:02:06 innobackupex: All tables unlocked 130307 21:02:06 innobackupex: Connection to database server closed innobackupex: Backup created in directory '/tmp/data' innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 107 innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream. 130307 21:02:06 innobackupex: completed OK! |
(3)恢复数据库
先关闭mysql服务,然后再删除test数据库文件
root@client2:/tmp/data# service mysql stop mysql stop/waiting root@client2:/tmp/data# cd /var/lib/mysql/ root@client2:/var/lib/mysql# ll total 77860 drwx------ 8 mysql mysql 4096 Mar 7 20:59 ./ drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ -rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ -rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ -rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ drwx------ 2 mysql mysql 4096 Mar 7 21:00 test/ drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/ root@client2:/var/lib/mysql# rm -rf test root@client2:/var/lib/mysql# ll total 77856 drwx------ 7 mysql mysql 4096 Mar 7 21:03 ./ drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ -rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ -rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ -rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
开始恢复数据库
先把之前/tmp/data里的数据库压缩版给解压
root@client2:cd /tmp/data root@client2:/tmp/data# tar -izxvf test-201303072101.tar.gz -C /tmp/restore/ backup-my.cnf ibdata1 xtrabackup_binlog_info test/test.frm test/db.opt xtrabackup_logfile xtrabackup_checkpoints xtrabackup_binary
注意:这里tar解包必须使用-i参数,否则解压出来的文件只有一个backup-my.cnf
查看一下restore的内容
root@client2:/tmp/data# cd /tmp/restore/ root@client2:/tmp/restore# ll total 67616 drwxr-xr-x 3 root root 4096 Mar 7 21:03 ./ drwxrwxrwt 14 root root 4096 Mar 7 21:03 ../ -rw-r--r-- 1 root root 260 Mar 7 21:01 backup-my.cnf -rw-rw---- 1 mysql mysql 69206016 Mar 7 21:01 ibdata1 drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ -rw-r--r-- 1 root root 13 Mar 7 21:02 xtrabackup_binary -rw-r--r-- 1 root root 23 Mar 7 21:02 xtrabackup_binlog_info -rw-r--r-- 1 root root 79 Mar 7 21:02 xtrabackup_checkpoints -rw-r--r-- 1 root root 2560 Mar 7 21:02 xtrabackup_logfile然后将备份文件中的日志应用到备份文件中的数据文件上
root@client2:/tmp/restore# innobackupex --user=root --password=123456 --apply-log /tmp/restore/ InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Inc 2009-2012. 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!". 130307 21:04:18 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined) xtrabackup: cd to /tmp/restore xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59605543) 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 130307 21:04:19 InnoDB: Using Linux native AIO xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 130307 21:04:19 InnoDB: The InnoDB memory heap is disabled 130307 21:04:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130307 21:04:19 InnoDB: Compressed tables use zlib 1.2.3 130307 21:04:19 InnoDB: Using Linux native AIO 130307 21:04:19 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 130307 21:04:19 InnoDB: Initializing buffer pool, size = 100.0M 130307 21:04:19 InnoDB: Completed initialization of buffer pool 130307 21:04:19 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! 130307 21:04:19 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 107, file name /var/log/mysql/mysql-bin.000022 130307 21:04:20 InnoDB: Waiting for the background threads to start 130307 21:04:21 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59605543 [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 107, file name /var/log/mysql/mysql-bin.000022 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 130307 21:04:21 InnoDB: Starting shutdown... 130307 21:04:25 InnoDB: Shutdown completed; log sequence number 59606758 130307 21:04:25 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore for creating ib_logfile* xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined) xtrabackup: cd to /tmp/restore 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 = 2 xtrabackup: innodb_log_file_size = 5242880 130307 21:04:25 InnoDB: Using Linux native AIO xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 130307 21:04:25 InnoDB: The InnoDB memory heap is disabled 130307 21:04:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130307 21:04:25 InnoDB: Compressed tables use zlib 1.2.3 130307 21:04:25 InnoDB: Using Linux native AIO 130307 21:04:25 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 130307 21:04:25 InnoDB: Initializing buffer pool, size = 100.0M 130307 21:04:25 InnoDB: Completed initialization of buffer pool 130307 21:04:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 130307 21:04:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... 130307 21:04:25 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! 130307 21:04:25 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 107, file name /var/log/mysql/mysql-bin.000022 130307 21:04:26 InnoDB: Waiting for the background threads to start 130307 21:04:27 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59607052 [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 107, file name /var/log/mysql/mysql-bin.000022 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 130307 21:04:27 InnoDB: Starting shutdown... 130307 21:04:31 InnoDB: Shutdown completed; log sequence number 59607052 130307 21:04:31 innobackupex: completed OK!
这里的--apply-log指明是将日志应用到数据文件上,完成之后将备份文件中的数据恢复到数据库中:
然后再查看一下当前目录内容
root@client2:/tmp/restore# ll total 79904 drwxr-xr-x 3 root root 4096 Mar 7 21:04 ./ drwxrwxrwt 14 root root 4096 Mar 7 21:04 ../ -rw-r--r-- 1 root root 260 Mar 7 21:01 backup-my.cnf -rw-rw---- 1 mysql mysql 69206016 Mar 7 21:04 ibdata1 -rw-r--r-- 1 root root 5242880 Mar 7 21:04 ib_logfile0 -rw-r--r-- 1 root root 5242880 Mar 7 21:04 ib_logfile1 drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ -rw-r--r-- 1 root root 13 Mar 7 21:02 xtrabackup_binary -rw-r--r-- 1 root root 23 Mar 7 21:02 xtrabackup_binlog_info -rw-r--r-- 1 root root 36 Mar 7 21:04 xtrabackup_binlog_pos_innodb -rw-r--r-- 1 root root 79 Mar 7 21:04 xtrabackup_checkpoints -rw-r--r-- 1 root root 2097152 Mar 7 21:04 xtrabackup_logfile 然后把test目录复制到/var/lib/mysql目录 root@client2:/tmp/restore# rsync -avz test ib* /var/lib/mysql/ sending incremental file list test/ test/db.opt test/test.frm sent 381 bytes received 54 bytes 870.00 bytes/sec total size is 8621 speedup is 19.82 root@client2:/tmp/restore# cd /var/lib/mysql/ root@client2:/var/lib/mysql# ll total 77860 drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./ drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ -rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ -rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ -rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
然后再修改test的用户与组为mysql
root@client2:/var/lib/mysql# chown -R mysql:mysql test root@client2:/var/lib/mysql# ll total 77860 drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./ drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ -rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ -rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ -rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:03 test/ drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
然后启动mysql,并查看test数据库的表里内容
root@client2:/var/lib/mysql# service mysql start mysql start/running, process 12730 root@client2:/var/lib/mysql# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) Copyright (c) 2000, 2012, 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 test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.01 sec)