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
Next revision
Previous revision
daily_issues [2011/04/14 01:43]
k2patel
daily_issues [2020/08/10 02:35] (current)
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 79: Line 88:
 Also same you can apply in my.cnf\\ Also same you can apply in my.cnf\\
  
-<​code ​mysql | Table Structure>​+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` ( CREATE TABLE `general_log` (
 `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,​ `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,​
Line 86: Line 98:
 `server_id` int(11) DEFAULT NULL, `server_id` int(11) DEFAULT NULL,
 `command_type` varchar(64) DEFAULT NULL, `command_type` varchar(64) DEFAULT NULL,
-`argument` mediumtext +`argument` mediumtext) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='​General log'
-) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='​General log'+
 </​code>​ </​code>​
  
 You can choose //​log_output//​ to be file OR table OR both. You can choose //​log_output//​ to be file OR table OR both.
  
-<​code ​mysql | Variable>​+<​code ​sql | Variable>​
 general_log=1 general_log=1
 log_output=TABLE,​FILE log_output=TABLE,​FILE
 </​code>​ </​code>​
  
-<​code ​mysql | Command >+Following command can help you apply changes without restart. 
 + 
 +<​code ​sql | Command >
 SET GLOBAL log_output='​TABLE';​ SET GLOBAL log_output='​TABLE';​
 SET GLOBAL general_log='​ON';​ 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>​ </​code>​
daily_issues.1302745397.txt.gz · Last modified: 2020/08/10 02:30 (external edit)