This shows you the differences between two versions of the page.
daily_issues [2018/08/19 05:15] k2patel [InnoDB: Unable to lock ./ibdata1, error: 11] |
daily_issues [2020/08/10 02:35] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ==== Mysql Upgrade Issue ==== | ||
- | ==== Easy Command List ==== | ||
- | |||
- | == How to check Type of Table == | ||
- | You can use any of the following command to determine table type. | ||
- | |||
- | <code sql> | ||
- | SHOW TABLE STATUS WHERE Name = 'table_name'; | ||
- | OR | ||
- | SHOW CREATE TABLE 'table_name'; | ||
- | OR | ||
- | SHOW TABLE STATUS; | ||
- | </code> | ||
- | |||
- | ===== Mysql Generate hash for password ===== | ||
- | <code mysql> | ||
- | SELECT PASSWORD('mypass'); | ||
- | </code> | ||
- | |||
- | OR | ||
- | |||
- | <code mysql> | ||
- | SELECT OLD_PASSWORD('mypass'); | ||
- | </code> | ||
- | |||
- | ==== MySQL Cluster information gathering ==== | ||
- | |||
- | === How to find which master log slave reading === | ||
- | <code sql> | ||
- | cat slaves | while read i ; do echo $i; mysql -h$i -e "SHOW SLAVE STATUS \G" | grep "Relay_Master_Log_File"; done | ||
- | </code> | ||
- | |||
- | === How continue with the next statement in the replication === | ||
- | |||
- | NOTE : Make sure you run the query manually on slave if it is query issue. | ||
- | |||
- | <code sql> | ||
- | STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; | ||
- | </code> | ||
- | |||
- | ==== Issue / Solutions ==== | ||
- | |||
- | |||
- | == Â appeared after upgrade == | ||
- | |||
- | This issue is appeared due to the default character set.\\ | ||
- | As Both version of database diff. character set. | ||
- | To get rid of that character try to following steps. | ||
- | |||
- | <code text> | ||
- | 1. insert following line to your my.cnf | ||
- | set-variable=character-set-server=UTF8 | ||
- | 2. re-import database should fix the issue. | ||
- | </code> | ||
- | |||
- | == mysqldump does not save stored procedure / functions / triggers. == | ||
- | |||
- | By default mysqldump only store triggers not procedures/functions. | ||
- | |||
- | In order to include to your mysqldump there is switch \\ | ||
- | -R, --routines Dump stored routines (functions and procedures). | ||
- | |||
- | In order to include triggers to your mysqldump there is switch \\ | ||
- | --triggers Dump triggers for each dumped table | ||
- | |||
- | <code mysql> | ||
- | mysqldump -R -u <username> -p<password> <database> | ||
- | </code> | ||
- | |||
- | |||
- | == After upgrade from 4.0 to 4.1 see issue on charset == | ||
- | |||
- | Use following line in your my.cnf will resolve the issue. | ||
- | as latin1 is default character set in 4.0 and 4.1 has utf8 | ||
- | |||
- | <code text | my.cnf> | ||
- | set-variable=character-set-server=latin1 | ||
- | </code> | ||
- | |||
- | |||
- | ==== General Log ==== | ||
- | |||
- | It log each and every query.\\ | ||
- | You can enable it in TABLE or FILE.\\ | ||
- | |||
- | Following method shows how to enable it in table without restarting,\\ | ||
- | Also same you can apply in my.cnf\\ | ||
- | |||
- | Structure usually created with mysql installation if you don't see could be old structure.\\ | ||
- | try running //mysql_upgrade// | ||
- | |||
- | <code sql | Table Structure> | ||
- | CREATE TABLE `general_log` ( | ||
- | `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
- | `user_host` mediumtext, | ||
- | `thread_id` int(11) DEFAULT NULL, | ||
- | `server_id` int(11) DEFAULT NULL, | ||
- | `command_type` varchar(64) DEFAULT NULL, | ||
- | `argument` mediumtext) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' | ||
- | </code> | ||
- | |||
- | You can choose //log_output// to be file OR table OR both. | ||
- | |||
- | <code sql | Variable> | ||
- | general_log=1 | ||
- | log_output=TABLE,FILE | ||
- | </code> | ||
- | |||
- | Following command can help you apply changes without restart. | ||
- | |||
- | <code sql | Command > | ||
- | SET GLOBAL log_output='TABLE'; | ||
- | SET GLOBAL general_log='ON'; | ||
- | </code> | ||
- | |||
- | How to flush log. | ||
- | |||
- | <code sql | Flush log> | ||
- | mysql -u root -pp4ssw0rd -e "TRUNCATE TABLE mysql.general_log" | ||
- | </code> | ||
- | |||
- | ==== mysql dump only partition information ==== | ||
- | If you were planning to drop a partition, you may need to back it up. find out the range of the partition by | ||
- | |||
- | <code> | ||
- | show create table tablename | ||
- | </code> | ||
- | |||
- | replace the range fields as follows: | ||
- | <code> | ||
- | mysqldump -S --no-create-info --where="RANGE(day) >= lowrange AND RANGE(day) < highrange" database_name table_name > /jtmp/table.sql | ||
- | </code> | ||
- | |||
- | ==== Reading Binlog ==== | ||
- | |||
- | Simple command to read binlog is | ||
- | <code bash> | ||
- | mysqlbinlog mysql-bin.000013 | ||
- | </code> | ||
- | |||
- | You can use following regularly used options. | ||
- | <code bash> | ||
- | --start-datetime= | ||
- | --start-position= | ||
- | --server-id= | ||
- | </code> | ||
- | |||
- | ==== Can't open file ==== | ||
- | |||
- | I've started seeing following error in my log.\\ | ||
- | |||
- | <code text> | ||
- | [ERROR] /usr/libexec/mysqld: Can't open file: | ||
- | </code> | ||
- | |||
- | It is caused by file discriptor limit set by the system.\\ | ||
- | You can check that by changing your shell to mysql user and running following command. | ||
- | |||
- | <code bash> | ||
- | ulimit -a | ||
- | </code> | ||
- | |||
- | In order to increase the open file limit.\\ | ||
- | edit following file.\\ | ||
- | |||
- | <code bash /etc/security/limits.conf> | ||
- | mysql soft nofile 49152 | ||
- | mysql hard nofile 65536 | ||
- | </code> | ||
- | |||
- | Now you can check mysql for limit seen by mysql.\\ | ||
- | login to mysql as root and run following command.\\ | ||
- | |||
- | <code mysql> | ||
- | show GLOBAL VARIABLES LIKE "open%"; | ||
- | </code> | ||
- | |||
- | Now you can modify it to increased limit in limits.conf.\\ | ||
- | by editing following file. | ||
- | |||
- | <code bash /etc/my.cnf> | ||
- | [mysqld] | ||
- | open-files-limit=49152 | ||
- | |||
- | [mysqld_safe] | ||
- | open-files-limit=49152 | ||
- | </code> | ||
- | |||
- | restart mysql and check again it should represent new value. | ||
- | |||
- | ==== InnoDB: Unable to lock ./ibdata1, error: 11 ==== | ||
- | |||
- | I've stumble across this issue while running mariadb server in VM Guest.\\ | ||
- | I believe this happens sometime when VDP is doing backup or machine is being migrated by DRS. | ||
- | |||
- | In my case, i've enable innodb per tables. So i've to clear lock from all files in subfolder.\\ | ||
- | In order to fix the issue, following command usually fix the issue. | ||
- | <code bash> | ||
- | systemctl stop mariadb | ||
- | cd /var/lib/mysql | ||
- | for i in `find . -name '*.ibd'`; do mv $i $i.bk; cp -a $i.bk $i ; rm $i.bk ; done | ||
- | systemctl start mariadb | ||
- | </code> |