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