一.基本信息 生产环境mysql版本：mariadb 10.0.20 备份xtrabackup版本：xtrabackup 2.2.9 或 2.4.4 测试恢复环境mysql版本：mariadb 10.0.20 恢复xtrabackup版本：xtrabackup 2.2.11 或 2.4.4 二.问题描述 用xtrabckup将生产环境的备份在异机上恢复后，能正常启动mysql，但在查看表结构时，提示表不存在。 三.问题分析 可以重现报借，如下是具体步骤： 1.将备份集从生产环境中copy到测试机，并更改owner权限 drwxr-xr-x 13 root root 4096 Nov 18 09:36 xtrabackup_20161117010002 chown -R apps:apps xtrabackup_20161117010002 2.解压备份集 innobackupex --decompress /apps/backup/xtrabackup_20161117010002 ...................... 161118 09:42:55 innobackupex: completed OK! 上面显示OK，表示成功解压 3.应用日志准备备份集 innobackupex --apply-log /apps/backup/xtrabackup_20161117010002 .................... xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 54808221346 161118 09:46:24 innobackupex: completed OK! 上面显示OK，表示成功备份集成功应用日志 4.将备份集还原到mysql对应数据目录 innobackupex --defaults-file=/apps/dbdat/my3306.cnf --copy-back /apps/backup/xtrabackup_20161117010002 ................................................ innobackupex: Starting to copy InnoDB system tablespace innobackupex: in '/apps/backup/xtrabackup_20161117010002' innobackupex: back to original InnoDB data directory '/apps/dbdat/mariadb10_data3306' innobackupex: Copying '/apps/backup/xtrabackup_20161117010002/ibdata1' to '/apps/dbdat/mariadb10_data3306/ibdata1' innobackupex: Starting to copy InnoDB undo tablespaces innobackupex: in '/apps/backup/xtrabackup_20161117010002' innobackupex: back to '/apps/dbdat/mariadb10_data3306' innobackupex: Starting to copy InnoDB log files innobackupex: in '/apps/backup/xtrabackup_20161117010002' innobackupex: back to original InnoDB log directory '/apps/dbdat/mariadb10_data3306' innobackupex: Copying '/apps/backup/xtrabackup_20161117010002/ib_logfile2' to '/apps/dbdat/mariadb10_data3306/ib_logfile2' innobackupex: Copying '/apps/backup/xtrabackup_20161117010002/ib_logfile0' to '/apps/dbdat/mariadb10_data3306/ib_logfile0' innobackupex: Copying '/apps/backup/xtrabackup_20161117010002/ib_logfile1' to '/apps/dbdat/mariadb10_data3306/ib_logfile1' innobackupex: Copying '/apps/backup/xtrabackup_20161117010002/ib_logfile3' to '/apps/dbdat/mariadb10_data3306/ib_logfile3' innobackupex: Finished copying back files. 161118 09:51:30 innobackupex: completed OK! 上面显示OK，表示成功copy备份集 5.启动mysql /apps/svr/mariadb10/bin/mysqld_safe --defaults-file=/apps/dbdat/my3306.cnf & 检查是否有成功启动： a 检查进程 [apps@m1234 ~]$ ps -ef|grep mysql apps 21281 21204 0 09:52 pts/0 00:00:00 /bin/sh /apps/svr/mariadb10/bin/mysqld_safe --defaults-file=/apps/dbdat/my3306.cnf apps 21837 21281 11 09:52 pts/0 00:00:01 /apps/svr/mariadb10/bin/mysqld --defaults-file=/apps/dbdat/my3306.cnf --basedir=/apps/svr/mariadb10 --datadir=/apps/dbdat/mariadb10_data3306 --plugin-dir=/apps/svr/mariadb10/lib/plugin --log-error=/apps/logs/mysql/error3306.log --open-files-limit=8192 --pid-file=/apps/dbdat/mariadb10_data3306/mysql.pid --socket=/tmp/mysql3306.sock --port=3306 apps 21906 21204 0 09:53 pts/0 00:00:00 grep mysql b.检查端口 [apps@m1234 ~]$ netstat -an|grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN unix 2 [ ACC ] STREAM LISTENING 15423289 /tmp/mysql3306.sock c.检查错误日志 161118 09:52:48 mysqld_safe Starting mysqld daemon with databases from /apps/dbdat/mariadb10_data3306 161118 9:52:49 [Note] /apps/svr/mariadb10/bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 21837 ... 2016-11-18 09:52:49 7f56e11d0760 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 161118 9:52:49 [Note] InnoDB: Using mutexes to ref count buffer pool pages 161118 9:52:49 [Note] InnoDB: The InnoDB memory heap is disabled 161118 9:52:49 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 161118 9:52:49 [Note] InnoDB: Memory barrier is not used 161118 9:52:49 [Note] InnoDB: Compressed tables use zlib 1.2.3 161118 9:52:49 [Note] InnoDB: Using Linux native AIO 161118 9:52:49 [Note] InnoDB: Using CPU crc32 instructions 161118 9:52:49 [Note] InnoDB: Initializing buffer pool, size = 21.0G 161118 9:52:50 [Note] InnoDB: Completed initialization of buffer pool 161118 9:52:50 [Note] InnoDB: Highest supported file format is Barracuda. 161118 9:52:51 [Note] InnoDB: 128 rollback segment(s) are active. 161118 9:52:51 [Note] InnoDB: Waiting for purge to start 161118 9:52:51 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 54808221346 161118 9:52:51 [Note] Plugin 'FEEDBACK' is disabled. 161118 9:52:51 [Note] Server socket created on IP: '0.0.0.0'. 161118 9:52:51 [Note] Event Scheduler: Loaded 0 events 161118 9:52:51 [Note] Event Scheduler: scheduler thread started with id 2 161118 9:52:51 [Note] /apps/svr/mariadb10/bin/mysqld: ready for connections. Version: '10.0.20-MariaDB-log' socket: '/tmp/mysql3306.sock' port: 3306 MariaDB Server 6.登入mysql查询表 /apps/svr/mariadb10/bin/mysql -uroot -p -S /tmp/mysql3306.sock MariaDB [(none)]> use ie2 Database changed MariaDB [ie2]> show tables; MariaDB [ie2]> desc Dos -> ; ERROR 1146 (42S02): Table 'ie2.Dos' doesn't exist MariaDB [ie2]> desc `MetaData`; ERROR 1146 (42S02): Table 'ie2.metadata' doesn't exist ---检查磁盘上的数据文件 [apps@m1234 mariadb10_data3306]$ cd ie2 [apps@m1234 ie2]$ ls -ltr total 12988 -rw-rw-r-- 1 apps apps 131072 Nov 18 09:51 Dos.ibd -rw-rw-r-- 1 apps apps 9921 Nov 18 09:51 Dos.frm -rw-rw-r-- 1 apps apps 61 Nov 18 09:51 db.opt [apps@m1234 ie2]$ cat db.opt default-character-set=utf8 default-collation=utf8_general_ci 将生产环境的备份的配置文件与测试恢复环境用diff比较一下： [apps@m1234 dbdat]$ diff mariadb10_3306.cnf my3306.cnf 2,3c2,3 < mysqld=/apps/svr/mariadb10/bin/mysqld_safe < mysqladmin=/apps/svr/mariadb10/bin/mysqladmin --- > mysqld=/apps/svr/mariadb101/bin/mysqld_safe > mysqladmin=/apps/svr/mariadb101/bin/mysqladmin 5c5 < log=/apps/logs/mysql/multi.log --- > log=/apps/logs/mysql/multi3306.log 33c33 < server-id=621133306 --- > server-id=313306 107c107 < lower_case_table_names=0 --- > lower_case_table_names=1 212c212 < read_only=0 --- > read_only 218c218 < #slave_exec_mode= --- > #c_mode= 221,231c221 < slave_parallel_threads=0 # for mariadb10 < < #server-id = 14 < #log-bin = slave-bin.log < < #replicate_wild_do_table = ie2.% --- > #slave_parallel_threads=4 # for mariadb10 234c224 < slave_compressed_protocol=0 #for RBR --- > #slave_compressed_protocol #for RBR 255c245 < expire_logs_days=3 --- > expire_logs_days=7 313c303 < innodb_buffer_pool_size=24G #for mariadb --- > innodb_buffer_pool_size=21G #for mariadb 328d317 < innodb_io_capacity_max=2400 335d323 < innodb_lru_scan_depth=1024 340c328 < innodb_file_per_table --- > innodb_file_per_table=1 384c372 < innodb_thread_concurrency=32 #at least equal cpu nums --- > innodb_thread_concurrency=8 #at least equal cpu nums 408c396 < key_buffer_size=2048M --- > key_buffer_size=1024M 437c425 < myisam_max_sort_file_size=10G --- > myisam_max_sort_file_size=5G 472c460 < #federated --- > federated 492d479 < max_allowed_packet = 2G # add by alading on 20151110 499,500c486,487 < key_buffer = 512M < sort_buffer_size = 512M --- > key_buffer = 256M > sort_buffer_size = 256M 511a499 > 上面配置比较，在于下面参数不同： < lower_case_table_names=0 > lower_case_table_names=1 大小写问题，改成 0 去试一下。 四.问题解决 将配置文件修改lower_case_table_names=0 ，并重启mysql实例。 然后登入mysql查看,不再报错。 lower_case_table_names=1时，会将表名转换成小写来查找和存储。如果希望在数据库里面创建表的时候保留大小写字符状态，则应该把这个参数置0： lower_case_table_names=0。
