Archive

Archive for the ‘MySQL DB’ Category

resolved – Starting MySQL.The server quit without updating PID file (/var/lib/mysql/testvm.pid).

April 3rd, 2015 Comments off

Today when I tried to start mysql it failed with below error:

[root@testvm ~]# /etc/init.d/mysql start
Starting MySQL.The server quit without updating PID file (/var/lib/mysql/testvm.pid).

First I had a check of /var/lib/mysql/testvm.err, and it had below entries:

2015-04-03 00:11:39 2925 [Note] InnoDB: Using CPU crc32 instructions
/usr/sbin/mysqld: Can't create/write to file '/tmp/ibDvk6bb' (Errcode: 13 - Permission denied)
2015-04-03 00:11:39 7f28af6c6720 InnoDB: Error: unable to create temporary file; errno: 13
2015-04-03 00:11:39 2925 [ERROR] Plugin 'InnoDB' init function returned error.
2015-04-03 00:11:39 2925 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-04-03 00:11:39 2925 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-04-03 00:11:39 2925 [ERROR] Aborting

I had a check of /tmp permission, and it's not correct:

[root@testvm ~]# ls -ld /tmp
drwx------ 19 root root 4096 Apr 3 07:15 /tmp

So I changed permission for /tmp to 777 with sticky bit:

[root@testvm ~]# chmod 1777 /tmp

[root@testvm ~]# ls -ld /tmp
drwxrwxrwt 19 root root 4096 Apr 3 07:15 /tmp

However, when I tried start mysql, it failed again with below errors in /var/lib/mysql/testvm.err:

2015-04-03 00:20:42 18724 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
2015-04-03 00:20:42 18724 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2015-04-03 00:20:42 18724 [ERROR] Plugin 'InnoDB' init function returned error.
2015-04-03 00:20:42 18724 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-04-03 00:20:42 18724 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-04-03 00:20:42 18724 [ERROR] Aborting

So it's all about InnoDB engine. As InnoDB was not required in our env, so I determined to disable InnoDB:

[root@testvm ~]# vi /etc/my.cnf
[mysqld]
innodb=OFF
ignore-builtin-innodb
skip-innodb
default-storage-engine=myisam
default-tmp-storage-engine=myisam

Later, the start of mysql succeeded.

Categories: Databases, IT Architecture, MySQL DB Tags:

mysql tips

July 1st, 2013 Comments off

Optimization: http://dev.mysql.com/doc/refman/5.1/en/optimization.html
HA: http://dev.mysql.com/doc/refman/5.1/en/ha-overview.html
Replication: http://dev.mysql.com/doc/refman/5.1/en/replication.html
Cluster: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html

log in:
mysql -h localhost -u root -pyourpassword

set password:
set password for 'root'@'localhost' = password('123456');

create db:
CREATE DATABASE `newone` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

add privileges:
CREATE USER 'root'@'192.168.0.0.1';
GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.0.0.1' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
flush privileges;

add user:
CREATE USER 'hello'@'192.168.0.2' IDENTIFIED BY '***';
GRANT ALL PRIVILEGES ON * . * TO 'hello'@'192.168.0.2' IDENTIFIED BY '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
flush privileges;

check user:
mysql> select Host,User from mysql.user;

export db(or you can use mysql enterprise backup<mysqlbackup, hot/warm/cold>):
mysqldump -u root -pyourpassword --no-data --default-character-set=utf8 test>aaa.sql #--no-data export only structures

import db:
mysql -h localhost -u root -pyourpassword dbname</root/db.sql

show storage engine of specified table

SELECT `ENGINE` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='ovs' AND `TABLE_NAME`='Mgr_NetworkFileServer';

SQL:

create table wp_posts_bak as select * from wp_posts;

select post_title,post_status,post_name from wp_posts where post_content like "%<pre>%";

update wp_posts set post_content=replace(post_content,'<blockquote>','<br /><i>');

DELIMITER // #when you need define some functions and met error "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1". You'll also need type END// instead of END;. Switch delimiter back to ; using DELIMITER ;

select length(post_content),char_length(post_content) from wp_posts;

PS:

  1. Below are comparision about mysql storage engine MyISAM & InnoDB - https://www.pureweber.com/article/myisam-vs-innodb/ & http://coolshell.cn/articles/652.html
  2. Here is about binlog format(row based/statement based/mixed, on slave it's relay logs) - http://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html
  3. We can use mysql proxy to make read/write separation. And we can use Mysql cluster for distributed HA/High redundant topology(shared nothing, need high bandwidth, RAC is shared disk architecture). For Oracle DB, we can use different service names for read/write separation.
Categories: Databases, IT Architecture, MySQL DB Tags:

change mysql proxy agent max-open-files

September 24th, 2012 Comments off

Change Step details
==============
1. Stop both the mysql service and mysql proxy agent on the SLAVE mysql host  using the startup script command:
sudo /etc/init.d/mysql stop
2. Change the user context to mysql user with the command:
sudo su - mysql
3. Update the mysql proxy agent configuration file "/apps/mysql/agent/etc/mysql-monitor-agent.ini", add a new line like below at the end of the file:
max-open-files = 8192
4. Under ROOT user context, update the mysql stratup script file "/etc/init.d/mysql", replace all the existing content there with the content below:
#!/bin/ksh
# chkconfig: 345 90 02
# description: MySQL startup
/apps/mysql/install/support-files/mysql.server $*
/apps/mysql/agent/etc/init.d/mysql-monitor-agent $*

5. Under ROOT user context, execute the command:
chkconfig --add mysql
6. Start both the mysql service and mysql proxy agent on the SLAVE mysql host using the startup script command:
sudo /etc/init.d/mysql start
7. Repeat the step#1 to #7 on the other MASTER mysql host

Categories: Databases, IT Architecture, MySQL DB Tags: