MySQL Problem solving

1,
In the MySQL error log:
140331 10:08:18 [ERROR] Error reading master configuration
140331 10:08:18 [ERROR] Failed to initialize the master info structure
140331 10:08:18 [Note] Event Scheduler: Loaded 0 events
 
From the error prompt, it can be seen that it is related to mster info. Find the master.info file in the data directory and directly view it by cat
# cat master.info
 
18
luocs-mysql-
 
bin.000004
267
1.1.1.1
rep1
PASSWORD
3306
60
0
 
 
 
 
 
0
1800.000
 
0
 
It can be seen that the format is incorrect. The solution is very simple, just reset slave. This will clear the master.info file and the error will disappear.
 
———————————————————————————————————————————————— —————–
2,
When adding a field to a large table, MySQL reports the following error:
ERROR 1799 (HY000) at line 1: Creating index ‘PRIMARY’ required more than ‘innodb_online_alter_log_max_size’ bytes of modification log. Please try again.
 
Solution:
My database is MySQL 5.5, and the innodb_online_alter_log_max_size value is 128M by default.
mysql> show variables like ‘innodb_online_alter_log_max_size’;
+ —————————————— + ————————- +
| Variable_name | Value |
+ —————————————— + ————————- +
| innodb_online_alter_log_max_size | 134217728 |
+ —————————————— + ————————- +
1 rows in set (0.00 sec)
 
This parameter is dynamic and global. You can increase it with the following command:
mysql> set global innodb_online_alter_log_max_size = 402653184;
Query OK, 0 rows affected (0.03 sec)
 
Add to the appropriate size, I added a field to the 120G size table to set the value to 4G, and successfully executed.
 
———————————————————————————————————————————————— —————–
3.
MySQL log:
140306 12:03:25 InnoDB: ERROR: the age of the last checkpoint is 9434024,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
 
It should be caused by the log size setting of the Innodb engine being too small, a certain thing generates a large number of logs, but the innodb_log_file_size setting is too small, you can increase the resolution.
 
Solution:
Directly find the method found online, as follows
STEP 01) Change the following in /etc/my.cnf
[mysqld]
innodb_log_buffer_size = 32M
innodb_buffer_pool_size = 3G
innodb_log_file_size = 768M
STEP 02) mysql -uroot -p -e "SET GLOBAL innodb_fast_shutdown = 0;"
STEP 03) service mysql stop
STEP 04) rm -f / var / lib / mysql / ib_logfile *
STEP 05) service mysql start
I added SET GLOBAL innodb_fast_shutdown = 0 ;. What does that do? It forces InnoDB to completely purge transactional changes from all of InnoDB moving parts, including the transactional logs (ib_logfile0, ib_logfile1). Thus, there is no need to backup the old ib_logfile0 , ib_logfile1. If deleting them makes you nervous, then make Step 04
 
mv / var / lib / mysql / ib_logfile * ..
 
———————————————————————————————————————————————— —————–
4.
When adding a field using the pt-online-schema-change tool, I get the error:
# pt-online-schema-change --alter = "add column tag_common text default null" --user = root --password = xxxxxxxx D = MYDB, t = MYTB --execute
Cannot connect to D = lsedata_13Q1, h = 10.13.7.47, p = ..., u = root
No slaves found. See --recursion-method if host BJL1-Y13-10-ops.gaoder.net has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
 
# A software update is available:
# * Percona Toolkit 2.2.6 has a possible security issue (CVE-2014-2029) upgrade is recommended. The current version for Percona :: Toolkit is 2.2.7.
 
The table `MYDB`.`MYTB` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.
 
This is the reason for the trigger on the MYTB table. You can learn from the working mechanism of pt-online-schema-change:
 
1) If there is a foreign key, detect the foreign key related table according to the alter-foreign-keys-method parameter value, and process it according to the corresponding setting;
2) Create a new table, the modified data table table structure, used to import data from the source data table to the new table;
3) Create a trigger to record the data modification operations on the source data table after the data replication starts, so that these operations can be performed after the data replication is completed to ensure that the data will not be lost;
4) Copy data, copy data from the source data table to the new table;
5) Modify the child table related to the foreign key, and modify the child table related to the foreign key according to the modified data;
6) Change the source data table to the old table, change the new table to the source table name, and delete the old table;
7) delete trigger;
 
pt-online-schema-change detailed document, please read: http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html
 
———————————————————————————————————————————————— —————–
5.
We may encounter the following errors when using mysqldump:
mysqldump: Got error: 1044: Access denied for user ‘lseread’ @ ‘IP’ to database ‘lsedata_13q1’ when doing LOCK TABLES
 
Solution:
Just add -skip-lock-tables, similar to the following:
mysqldump -h1.1.1.1 -uuser -ppassword -P3306 mydb mytb --where "time <= cast ('2014-04-03 16:00' as datetime)" --skip-lock-tables --default-character-set = utf8> mytb.txt
 
MySQL5.6 has started to provide a new feature GTID mode. Our developers encountered the following warning when dumping from the library I provided:
Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged = OFF To make a complete dump, pass --all-databases --triggers --routines --events.
 
Although there are warnings, the data can still be dumped. Depressingly, when you want to import the dump file into other versions of the database, you will encounter:
ERROR 1839 (HY000) at line 24: @@ GLOBAL.GTID_PURGED can only be set when @@ GLOBAL.GTID_MODE = ON.
 
Refer to the following:
gtid_executed:
WHEN used WITH global scope, this variable contains a representation OF the SET OF ALL transactions that are logged IN the BINARY log.
WHEN used WITH SESSION scope, it contains a representation OF the SET OF transactions that are written TO the cache IN the CURRENT SESSION.
Issuing RESET MASTER causes the global VALUE (but NOT the SESSION VALUE) OF this variable TO be reset TO an empty string.
 
Solution:
Add the parameter -gtid-mode = OFF when dumping, similar to the following:
mysqldump -h1.1.1.1 -uuser -ppassword -P3306 mydb mytb --where "time <= cast ('2014-04-03 16:00' as datetime)" --skip-lock-tables --default-character-set = utf8 --gtid-mode = OFF> mytb.txt
 
———————————————————————————————————————————————— —————–
6.
Adding a field to a large table, the system HANG lived after a while, adding a field is interrupted. After the system restarts, I want to add fields again, but encounter the following error:
mysql> alter table mytb add column yyy text default null;
ERROR 1050 (42S01): Table ‘mydb / # sql-ib54’ already exists
 
View the MySQL err log:
2014-04-04 09:10:12 10578 [Note] /opt/mysql5.6/bin/mysqld: ready for connections.
Version: ‘5.6.17-log’ socket: ‘/opt/mysql5.6/data/mysql.sock’ port: 3307 Source distribution
2014-04-04 09:10:24 10578 [ERROR] InnoDB: Failed to find tablespace for table ‘" mydb "." # Sql-ib54 "‘ in the cache. Attempting to load the tablespace with space id 54.
2014-04-04 09:10:24 52e55940 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2014-04-04 09:10:24 10578 [ERROR] InnoDB: Could not find a valid tablespace file for 'mydb / # sql-ib54'. See http://dev.mysql.com/doc/refman/5.6/ en / innodb-troubleshooting-datadict.html for how to resolve the issue.
 
Looking at the data directory, there are some files starting with #, as follows:
# ls
– Other table information is ignored. The reason why there are more .ibd files is because the operation table mytb is a partitioned table.
# sql-ib58.ibd # sql-ib65.ibd # sql-1935_2.frm # sql-ib59.ibd # sql-ib66.ibd
# sql-1935_2.par # sql-ib60.ibd # sql-ib67.ibd # sql-ib54.ibd # sql-ib61.ibd
# sql-ib55.ibd # sql-ib62.ibd # sql-ib56.ibd # sql-ib63.ibd # sql-ib57.ibd # sql-ib64.ibd
 
I encountered this problem for the first time. I didn't delve into the details. I just thought that the beginning of # is a temporary file. I think it can be rm, and I just deleted it without hesitation.
Then restart the database, and reported a large number of ERROR:
2014-04-04 09:10:12 2b1b9b20dfe0 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2014-04-04 09:10:12 10578 [ERROR] InnoDB: Could not find a valid tablespace file for 'mydb / # sql-ib54'. See http://dev.mysql.com/doc/refman/5.6/ en / innodb-troubleshooting-datadict.html for how to resolve the issue.
2014-04-04 09:10:12 10578 [ERROR] InnoDB: Tablespace open failed for ‘" mydb "." # Sql-ib54 "‘, ignored.
2014-04-04 09:10:12 2b1b9b20dfe0 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2014-04-04 09:10:12 10578 [ERROR] InnoDB: Could not find a valid tablespace file for 'mydb / # sql-ib55'. See http://dev.mysql.com/doc/refman/5.6/ en / innodb-troubleshooting-datadict.html for how to resolve the issue.
2014-04-04 09:10:12 10578 [ERROR] InnoDB: Tablespace open failed for ‘" mydb "." # Sql-ib55 "‘, ignored.
...
 
I need to calm down when I encounter a problem. I try to add fields, but the error still occurs.
Then I read the http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html article given in the error log, and I learned that in the independent table space mode, the shell This problem is encountered when the command deletes the table structure and table space files.
 
The rescue method is also given in the article, and I found the following paragraphs:
Problem with Temporary Table
 
If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. Using the Table Monitor, you can see listed a table with a name that begins with # sql-. You can perform SQL statements on tables whose name contains the character "#" if you enclose the name within backticks. Thus, you can drop such an orphaned table like any other orphaned table using the method described earlier. To copy or rename a file in the Unix shell , you need to put the file name in double quotation marks if the file name contains "#".
 
I created a new library, created a table with the same structure, and then added fields to the table. At this time, files starting with # sql- were generated in the new database data directory. I copied all these files to the original library. Under contents.
Then renamed as follows:
# mv \ # sql-2ff9_1.frm sql-2ff9_1.frm
# mv \ # sql-2ff9_1.par sql-2ff9_1.par
# mv \ # sql-ib82.ibd sql-ib82.ibd
# mv \ # sql-ib83.ibd sql-ib83.ibd
# mv \ # sql-ib84.ibd sql-ib84.ibd
# mv \ # sql-ib85.ibd sql-ib85.ibd
# mv \ # sql-ib86.ibd sql-ib86.ibd
# mv \ # sql-ib87.ibd sql-ib87.ibd
# mv \ # sql-ib88.ibd sql-ib88.ibd
# mv \ # sql-ib89.ibd sql-ib89.ibd
# mv \ # sql-ib90.ibd sql-ib90.ibd
# mv \ # sql-ib91.ibd sql-ib91.ibd
# mv \ # sql-ib92.ibd sql-ib92.ibd
# mv \ # sql-ib93.ibd sql-ib93.ibd
# mv \ # sql-ib94.ibd sql-ib94.ibd
# mv \ # sql-ib95.ibd sql-ib95.ibd
 
In this way, show tables can view the table information:
 
mysql> show tables;
+ ———————— +
| Tables_in_mydb |
+ ———————— +
| # mysql50 # sql-2ff9_1 |
+ ———————— +
1 rows in set (0.01 sec)
 
But tragically, I tried to delete the table # mysql50 # sql-2ff9_1, but I still could not delete it.
Time is also a lot, and development has been asking about progress.
I thought of other plans:
For the table rename, create a new table with the same structure, import data to the new table, add fields to the new table, and delete the rename table.
 
It has been proved that this scheme is feasible. That is, the amount of data is large, and the process of leading data takes a long time, of course, the time for adding fields is also the same.
 
Some people on the Internet said that deleting ibdata1 and ib_logfile0, 1, and then restarting the database can solve it. Is this method feasible?
The answer is of course NO. In the independent table space mode, ibdata stores the data dictionary and UNDO information. Restarting the database after deletion will generate a new ibdata, which means that the dictionary information and UNDO are lost.
 
At that time, the following phenomena will occur:
mysql> show tables;
+ ——————- +
| Tables_in_mydb |
+ ——————- +
| t1 |
+ ——————- +
1 rows in set (0.01 sec)
 
mysql> select count (*) from t1;
ERROR 1146 (42S02): Table ‘mydb.t1’ does n’t exist

———————————————————————————————————————————————— —————–
 

 

7.
The master-slave replication failed. Check the slave log for the following error:
140405 4:16:12 [ERROR]Slave I / O: error reconnecting to master ‘rep1@10.13.34.199: 3306’ – retry-time: 60 retries: 86400, Error_code: 2003
140405 6:53:12 [Note] Slave: connected to master ‘rep1@10.13.34.199: 3306’, replication resumed in log ‘mysql-bin.000275’ at position 192295247
140405 6:53:12 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno = 1236)
140405 6:53:12 [ERROR] Slave I / O: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’, Error_code: 1236
140405 6:53:12 [Note] Slave I / O thread exiting, read up to log ‘mysql-bin.000275’, position 192295247
140405 6:54:11 [Note] Error reading relay log event: slave SQL thread was killed
140405 6:54:11 [Note] Slave I / O thread: connected to master ‘rep1@10.13.34.199: 3306’, replication started in log ‘mysql-bin.000275’ at position 192295247
140405 6:54:11 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno = 1236)
140405 6:54:11 [ERROR] Slave I / O: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’, Error_code: 1236
140405 6:54:11 [Note] Slave I / O thread exiting, read up to log ‘mysql-bin.000275’, position 192295247
140405 6:54:11 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000275’ at position 192295247, relay log ‘./SHUBEI-34-198-relay-bin.000153’ position: 192295393
 
Find the specified position from the specified binlog, I found that it was over, and then I cut the new binlog
# mysqlbinlog mysql-bin.000275 | grep -A 10 192295247
# 140405 3:16:06 server id 1 end_log_pos 192295247 Xid = 468032712
COMMIT / *! * /;
DELIMITER;
# End of log file
ROLLBACK / * added by mysqlbinlog * /;
/ *! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE * /;
 
This problem is solved, and you can specify a new binlog for the slave.
 
Main library show master status;
mysql> show master status;
+ —————— + ————- + ————– + —————— +
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ —————— + ————- + ————– + —————— +
| mysql-bin.000276 | 107 | | test |
+ —————— + ————- + ————– + —————— +
1 row in set (0.00 sec)
 
Reconfigure replication from the library:
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> change master to master_host = ‘10 .13.34.199 ’, master_port = 3306, master_user =‘ rep1 ’, master_password =‘ RepSlavE & 2013 ’, master_log_file =‘ mysql-bin.000276 ’, master_log_pos = 107;
Query OK, 0 rows affected (0.05 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
View master-slave replication status:
mysql> show slave status \ G
—-Ellipsis—-
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 

———————————————————————————————————————————————— —————–
8,
Solve ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
After modifying the output format of relay-log, restarting MySQL failed to synchronize
 
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
 
MySQL Error log:
2014-02-20 16:35:19 27094 [ERROR] Failed to open the relay log ‘./luocs166-relay-bin.000007’ (relay_log_pos 359).
2014-02-20 16:35:19 27094 [ERROR] Could not find target log file mentioned in relay log info in the index file ‘/opt/mysql/data/anav-relay-log.index’ during relay log initialization.
 
Solution:
I found the following paragraph when I looked at the change master to command
mysql>? change master to
– Omit most of
The next example shows an operation that is less frequently employed.
It is used when the slave has relay log files that you want it to
execute again for some reason. To do this, the master need not be
reachable. You need only use CHANGE MASTER TO and start the SQL thread
(START SLAVE SQL_THREAD):
 
CHANGE MASTER TO
  RELAY_LOG_FILE = ‘slave-relay-bin.006’,
  RELAY_LOG_POS = 4025;
 
But my database version is 5.6, using GTID mode, using the error:
mysql> CHANGE MASTER TO
    -> RELAY_LOG_FILE = ‘anav-relay-log.000001’,
    -> RELAY_LOG_POS = 120;
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
 
So the solution is as follows:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CHANGE MASTER TO
    -> MASTER_HOST = ‘10 .19.3.168 ’,
    -> MASTER_USER = ‘repl2’,
    -> MASTER_PASSWORD = ‘oracle’,
    -> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
mysql> show slave status \ G
…. Omissions….
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
MYSQL problem solving

label:

Original address: http://www.cnblogs.com/Yongzhouunknown/p/4853569.html