User Tools

Site Tools


daily_issues

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
daily_issues [2017/03/18 16:54]
k2patel [MySQL with where clause]
daily_issues [2017/07/01 22:20] (current)
k2patel old revision restored (2016/05/18 18:26)
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.
daily_issues.txt · Last modified: 2017/07/01 22:20 by k2patel