This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
daily_issues [2011/02/14 10:28] k2patel |
daily_issues [2020/08/10 02:35] (current) |
||
---|---|---|---|
Line 6: | Line 6: | ||
You can use any of the following command to determine table type. | You can use any of the following command to determine table type. | ||
- | <code bash> | + | <code sql> |
SHOW TABLE STATUS WHERE Name = 'table_name'; | SHOW TABLE STATUS WHERE Name = 'table_name'; | ||
OR | OR | ||
Line 14: | Line 14: | ||
</code> | </code> | ||
+ | ===== Mysql Generate hash for password ===== | ||
+ | <code mysql> | ||
+ | SELECT PASSWORD('mypass'); | ||
+ | </code> | ||
+ | OR | ||
+ | |||
+ | <code mysql> | ||
+ | SELECT OLD_PASSWORD('mypass'); | ||
+ | </code> | ||
==== MySQL Cluster information gathering ==== | ==== MySQL Cluster information gathering ==== | ||
Line 71: | Line 80: | ||
+ | ==== 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> |