Shulou(Shulou.com)06/01 Report--

This article mainly introduces what mistakes often occur when mysql migration, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand what mistakes often occur when mysql migration bar.

Error 1: various problems often occur when the database is migrated, such as performance_schema and database perfor. You can try to fix it with this command:

Mysql_upgrade-u root-p mysql_upgrade-u root-p-- force

Error 2: the specified innodb directory cannot be found.

Add the following parameters to the my.cnf configuration file:

Innodb_data_home_dir = / home/data/databaseinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = / home/data/database

Here are the steps to properly migrate the database:

1) initialize the database directory: / datamysqld-- initialize-insecure-- basedir=/usr/local/mysql-- datadir=/home/data/database-- user=mysql-- server-id=502) Delete the files under the initialization directory: rm-rf ibdata1 iblogfile1 iblogfile2 mysql 3) copy the copied database to the initialization directory cp mysql ibdata1 iblogfile1 iblogfile2 / data/ 4) mysql_upgrade-u root-p # this step is very important and can generally solve all the problems during migration. Various errors may occur in the copied database. At this time, you need to set innodb_force_recovery=1 in the my.cnf configuration file, restart mysql, and change the error to 2 until 6.

Error 3: when the master-slave replication of the database configuration is finished, always prompt least set server_id:

Delete the 5 tables under mysql.

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

Then delete the related .frm .ibd file under / data/mysql # if not, there is no need to delete

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*

Log in to the database: > source / usr/local/mysql/share/mysql_system_tables.sql # if this command does not work, you need to rebuild the table.

Use mysqlCREATE 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_ substitution`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 `last_ 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 CREATE TABLE `Master_log_ 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.' `Bind`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` 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' 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.' `Id` 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' CREATE TABLE `Master_log_ info` (`Id` int (10) unsigned NOT NULL, `Relay_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Master_log_ name` 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_ 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'

Restart mysql.

Error: mysql.user has no `Event_ priv` column at position 28

/ usr/local/mysql/bin/mysqld-- defaults-file=/usr/local/mysql/my.cnf-- initialize-insecureselect user,host,authentication_string,plugin from mysql.user; # View user status 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'; # create a test account select user,host,authentication_string,plugin from mysql.user # user status or error 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

After reading the mistakes that often occur during mysql migration, many readers must have some understanding. If you need more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.