MySQL Error summary

Error 1: When a database is migrated, various problems often arise, such as performance_schema, database Perfor This error, 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 was not 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

Let's talk about the steps to properly migrate a database:

1) Initialize 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 initialized directory CP MySQL IBDATA1 iblogfile1 IB LOGFILE2/DATA/4) mysql_upgrade-u Root-p #这一步至关重要, it is generally possible to resolve all issues during migration.

A copy of the database can be a variety of errors, you need to set Innodb_force_recovery=1 in the MY.CNF configuration file, restart MySQL, if the error is changed to 21 until 6 try.

Error 3: The least set server_id is always prompted when the database configuration master-slave replication finishes migrating:

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;dro P table if exists slave_relay_log_info;drop table if exists slave_worker_info;

And then delete:/data/mysql under the relevant. frm. ibd file #如果没有则无需删除

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* 

login database: > source /usr/local/mysql/share/mysql_system_tables.sql

use mysqlcreate table  ' Innodb_index_stats '   (   ' database_name '  varchar (64)  COLLATE utf8_bin NOT NULL,   ' table_name '  varchar (+)  COLLATE  utf8_bin not null,   ' index_name '  varchar (+)  collate utf8_bin not  NULL,   ' Last_update '  timestamp not null default current_timestamp  ON UPDATE CURRENT_TIMESTAMP,   ' Stat_name '  varchar ( collate utf8_) bin not null,   ' Stat_value '  bigint ( unsigned NOT NULL,   ') Sample_size '  bigint  unsigned DEFAULT NULL,   ' stat_description '  varchar ( 1024x768)  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 (+)  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 ( unsigned NOT NULL,   ' clustered_index_size '  bigint ()  unsigned  NOT NULL,   ' sum_of_other_index_sizes '  bigint ( unsigned not null),   PRIMARY KEY  (' database_name ', ' table_name '))  engine=innodb default charset =utf8 collate=utf8_bin stats_persistent=0;

create table  ' Slave_master_info '   (   ' Number_of_lines '  int (Ten)  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 ( unsigned ) not null comment  ' The master log position of the last read  event. ',   ' Host '  char (+)  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 (Ten)  unsigned not null  COMMENT  ' The network port used to connect to the master. ',    ' Connect_retry '  int (Ten)  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&nBsp;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 ';

create table  ' Slave_relay_log_info '   (   ' Number_of_lines '  int (Ten)  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 ( 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 ( unsigned NOT ) null comment  ' the master log position of the last executed  event. ',   ' Sql_delay '  int (one)  NOT NULL COMMENT  ' the number of  seconds that the slave must lag behind the master. ',   ' Number_of_workers '  int (Ten)  unsigned NOT NULL,   ' Id '  int (Ten)  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  ' Slave_worker_info '   (   ' Id '  int (Ten)  unsigned NOT  null,   ' Relay_log_name '  text CHARACTER SET utf8 COLLATE utf8_bin  not null,   ' Relay_log_pos '  bigint ( unsigned NOT NULL,   ' Master _log_name '  text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,    ' Master_log_pos '  bigint  unsigned NOT NULL,   ' checkpoint_relay_log_name '  text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,   ' Checkpoint_relay_log_pos '  bigint ( unsigned NOT NULL,   ' Checkpoint_master_log_ Name '  text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,   ' Checkpoint_master_log_pos '  bigint ( unsigned NOT NULL,   ' checkpoint_seqno ')  int (Ten)  unsigned not null,   ' Checkpoint_group_size '  int (Ten)  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 have 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;     #查看用户状态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;

This article is from "Little East Brother" blog, please make sure to keep this source http://xiaodongge.blog.51cto.com/11636589/1844281

MySQL Error summary