错误1：数据库做迁移时，往往会出现各种问题，比如performance_schema，数据库perfor这个出错，可以尝试用此命令修复：
<code>mysql_upgrade -u root -p </code>
<code> </code>
<code>mysql_upgrade -u root -p --force</code>
<code></code>
错误2：找不到指定的innodb目录。
在my.cnf配置文件中添加如下参数：
<code>innodb_data_home_dir = </code><code>/home/data/database</code>
<code>innodb_data_file_path = ibdata1:10M:autoextend</code>
<code>innodb_log_group_home_dir = </code><code>/home/data/database</code>
这里说一下正确迁移数据库的步骤：
<code>1）初始化数据库目录：</code><code>/data</code>
<code>mysqld --initialize-insecure --basedir=</code><code>/usr/local/mysql</code> <code>--datadir=</code><code>/home/data/database</code> <code>--user=mysql --server-</code><code>id</code><code>=50</code>
<code>2）删除初始化目录下的文件：</code><code>rm</code> <code>-rf ibdata1 iblogfile1 iblogfile2 mysql </code>
<code>3）把复制的数据库拷贝到初始化的目录下</code><code>cp</code> <code>mysql ibdata1 iblogfile1 iblogfile2 </code><code>/data/</code>
<code>4）mysql_upgrade -u root -p </code><code>#这一步至关重要，一般能解决迁移时所有问题。</code>
<code>拷贝的数据库可能出现各种错误，此时需要在my.cnf配置文件中设置innodb_force_recovery＝1 ，重启mysql，如果出错改为2一直到6试试。</code>
错误3：迁移完数据库配置主从复制时，总是提示least set server_id:
删除mysql下的5张表。
<code>drop table </code><code>if</code> <code>exists innodb_index_stats;</code>
<code>drop table </code><code>if</code> <code>exists innodb_table_stats;</code>
<code>drop table </code><code>if</code> <code>exists slave_master_info;</code>
<code>drop table </code><code>if</code> <code>exists slave_relay_log_info;</code>
<code>drop table </code><code>if</code> <code>exists slave_worker_info;</code>
然后删除：/data/mysql下相关的.frm .ibd文件 #如果没有则无需删除
<code>rm</code> <code>-rf innodb_index_stats*</code>
<code>rm</code> <code>-rf innodb_table_stats*</code>
<code>rm</code> <code>-rf slave_master_info*</code>
<code>rm</code> <code>-rf slave_relay_log_info*</code>
<code>rm</code> <code>-rf slave_worker_info*</code>
登录数据库：> source /usr/local/mysql/share/mysql_system_tables.sql #此命令不行的话需要重新建表。
<code>use mysql</code>
<code>CREATE TABLE `innodb_index_stats` (</code>
<code> </code><code>`database_name` varchar(64) COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>`table_name` varchar(64) COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>`index_name` varchar(64) COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,</code>
<code> </code><code>`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>`stat_value` bigint(20) unsigned NOT NULL,</code>
<code> </code><code>`sample_size` bigint(20) unsigned DEFAULT NULL,</code>
<code> </code><code>`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)</code>
<code>) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;</code>
<code>-----------------------------------</code>
<code>CREATE TABLE `innodb_table_stats` (</code>
<code> </code><code>`n_rows` bigint(20) unsigned NOT NULL,</code>
<code> </code><code>`clustered_index_size` bigint(20) unsigned NOT NULL,</code>
<code> </code><code>`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,</code>
<code> </code><code>PRIMARY KEY (`database_name`,`table_name`)</code>
<code>CREATE TABLE `slave_master_info` (</code>
<code> </code><code>`Number_of_lines` int(10) unsigned NOT NULL COMMENT </code><code>'Number of lines in the file.'</code><code>,</code>
<code> </code><code>`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT </code><code>'The name of the master binary log currently being read from the master.'</code><code>,</code>
<code> </code><code>`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT </code><code>'The master log position of the last read event.'</code><code>,</code>
<code> </code><code>`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT </code><code>''</code> <code>COMMENT </code><code>'The host name of the master.'</code><code>,</code>
<code> </code><code>`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The user name used to connect to the master.'</code><code>,</code>
<code> </code><code>`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The password used to connect to the master.'</code><code>,</code>
<code> </code><code>`Port` int(10) unsigned NOT NULL COMMENT </code><code>'The network port used to connect to the master.'</code><code>,</code>
<code> </code><code>`Connect_retry` int(10) unsigned NOT NULL COMMENT </code><code>'The period (in seconds) that the slave will wait before trying to reconnect to the master.'</code><code>,</code>
<code> </code><code>`Enabled_ssl` tinyint(1) NOT NULL COMMENT </code><code>'Indicates whether the server supports SSL connections.'</code><code>,</code>
<code> </code><code>`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The file used for the Certificate Authority (CA) certificate.'</code><code>,</code>
<code> </code><code>`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The path to the Certificate Authority (CA) certificates.'</code><code>,</code>
<code> </code><code>`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The name of the SSL certificate file.'</code><code>,</code>
<code> </code><code>`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The name of the cipher in use for the SSL connection.'</code><code>,</code>
<code> </code><code>`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The name of the SSL key file.'</code><code>,</code>
<code> </code><code>`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT </code><code>'Whether to verify the server certificate.'</code><code>,</code>
<code> </code><code>`Heartbeat` float NOT NULL,</code>
<code> </code><code>`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'Displays which interface is employed when connecting to the MySQL server'</code><code>,</code>
<code> </code><code>`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The number of server IDs to be ignored, followed by the actual server IDs'</code><code>,</code>
<code> </code><code>`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The master server uuid.'</code><code>,</code>
<code> </code><code>`Retry_count` bigint(20) unsigned NOT NULL COMMENT </code><code>'Number of reconnect attempts, to the master, before giving up.'</code><code>,</code>
<code> </code><code>`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The file used for the Certificate Revocation List (CRL)'</code><code>,</code>
<code> </code><code>`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT </code><code>'The path used for Certificate Revocation List (CRL) files'</code><code>,</code>
<code> </code><code>`Enabled_auto_position` tinyint(1) NOT NULL COMMENT </code><code>'Indicates whether GTIDs will be used to retrieve events from the master.'</code><code>,</code>
<code> </code><code>PRIMARY KEY (`Host`,`Port`)</code>
<code>) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT=</code><code>'Master Information'</code><code>;</code>
<code>CREATE TABLE `slave_relay_log_info` (</code>
<code> </code><code>`Number_of_lines` int(10) unsigned NOT NULL COMMENT </code><code>'Number of lines in the file or rows in the table. Used to version table definitions.'</code><code>,</code>
<code> </code><code>`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT </code><code>'The name of the current relay log file.'</code><code>,</code>
<code> </code><code>`Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT </code><code>'The relay log position of the last executed event.'</code><code>,</code>
<code> </code><code>`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT </code><code>'The name of the master binary log file from which the events in the relay log file were read.'</code><code>,</code>
<code> </code><code>`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT </code><code>'The master log position of the last executed event.'</code><code>,</code>
<code> </code><code>`Sql_delay` int(11) NOT NULL COMMENT </code><code>'The number of seconds that the slave must lag behind the master.'</code><code>,</code>
<code> </code><code>`Number_of_workers` int(10) unsigned NOT NULL,</code>
<code> </code><code>`Id` int(10) unsigned NOT NULL COMMENT </code><code>'Internal Id that uniquely identifies this record.'</code><code>,</code>
<code> </code><code>PRIMARY KEY (`Id`)</code>
<code>) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT=</code><code>'Relay Log Information'</code><code>;</code>
<code>CREATE TABLE `slave_worker_info` (</code>
<code> </code><code>`Id` int(10) unsigned NOT NULL,</code>
<code> </code><code>`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>`Relay_log_pos` bigint(20) unsigned NOT NULL,</code>
<code> </code><code>`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>`Master_log_pos` bigint(20) unsigned NOT NULL,</code>
<code> </code><code>`Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>`Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,</code>
<code> </code><code>`Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,</code>
<code> </code><code>`Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,</code>
<code> </code><code>`Checkpoint_seqno` int(10) unsigned NOT NULL,</code>
<code> </code><code>`Checkpoint_group_size` int(10) unsigned NOT NULL,</code>
<code> </code><code>`Checkpoint_group_bitmap` blob NOT NULL,</code>
<code>) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT=</code><code>'Worker Information'</code><code>;</code>
重启mysql。
错误：mysql.user has no `Event_priv` column at position 28
<code>/usr/local/mysql/bin/mysqld</code> <code>--defaults-</code><code>file</code><code>=</code><code>/usr/local/mysql/my</code><code>.cnf --initialize-insecure</code>
<code>select</code> <code>user,host,authentication_string,plugin from mysql.user; </code><code>#查看用户状态</code>
<code>UPDATE mysql.user SET authentication_string=PASSWORD(</code><code>'password'</code><code>) WHERE User=</code><code>'root'</code> <code>and host=</code><code>'localhost'</code><code>;</code>
<code>FLUSH PRIVILEGES;</code>
<code>/usr/local/mysql/bin/mysqld_safe</code> <code>--defaults-</code><code>file</code><code>=</code><code>/usr/local/mysql/my</code><code>.cnf --skip-grant-tables &</code>
<code>/usr/local/mysql/bin/mysql_upgrade</code> <code>--defaults-</code><code>file</code><code>=</code><code>/usr/local/mysql/my</code><code>.cnf -p --force GRANT ALL PRIVILEGES ON *.* TO </code><code>'test1'</code><code>@</code><code>'localhost'</code> <code>IDENTIFIED BY </code><code>'password'</code><code>; </code><code>#创建一个测试用的账户</code>
<code>select</code> <code>user,host,authentication_string,plugin from mysql.user; </code><code>#用户状态还是错误</code>
<code>UPDATE mysql.user SET authentication_string=PASSWORD(</code><code>'password'</code><code>) WHERE User=</code><code>'test1'</code> <code>and host=</code><code>'localhost'</code><code>;</code>
<code>UPDATE mysql.user SET plugin=</code><code>'mysql_native_password'</code> <code>WHERE User=</code><code>'test1'</code> <code>and host=</code><code>'localhost'</code><code>;</code>
本文转自 王家东哥 51CTO博客，原文链接:http://blog.51cto.com/xiaodongge/1844281
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)