Category Archives: Mysql

Useful MySQL commands

To find MySQL root/admin pass :

cPanel server           : cat /root/.my.cnf ( username : root )
Plesk server             : cat /etc/psa/.psa.shadow ( username : admin )
DirectAdmin server  : cat /usr/local/directadmin/conf/mysql.conf

To login to MySQL :

mysql -u 'username' -p ( will prompt for password )
Password:

To create MySQL dump of a database :

mysqldump -u 'username' -p dbname > database_name.sql ( will prompt for password )

To create MySQL dump of all databases :

mysqldump -u 'username' -p --all-databases > all_databases.sql ( will prompt for password )

To restore all databases from the MySQL dump :

mysql -u username -p < all_databases.sql ( will prompt for password )

To restore a MySQL dump for a database :

mysql -u 'username' -p dbname < database_name.sql ( will prompt for password )

To restore a single database from dump of all databases :

mysql -u 'username' -p --one-database dbname < all_databases.sql ( will prompt for password )

To create MySQL dump of a single table in a database :

mysqldump -u 'username' -p dbname table_name > table_name.sql ( will prompt for password )

To restore the above table from MySQL dump :

mysql -u 'username' -p dbname < /path/to/table_name.sql ( will prompt for password )

One liner to truncate all tables in a db from MySQL :

mysql -Nse 'show tables' DBNAME | while read table;
do mysql -e "truncate table $table" DBNAME; done

One liner to drop all tables in a db from MySQL :

mysql -Nse 'show tables' DBNAME | while read table;
do mysql -e "drop table $table" DBNAME; done

MySQL server not starting ?

There are ton’s of causes for which MySQL might not start,
ranging from disk space full to databases getting corrupt.

First place where you have to check for a clue is the .err log
( /var/lib/mysql/hostname.err )

If the err corresponds to something like this :

InnoDB: End of page dump
140104 12:33:19 InnoDB: Page checksum 2288969011, prior-to-4.0.14-form checksum 2949853821
InnoDB: stored checksum 492713095, prior-to-4.0.14-form stored checksum 2949853821
InnoDB: Page lsn 0 40542, low 4 bytes of lsn at page end 40542
InnoDB: Page number (if stored to page already) 47,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 12
InnoDB: Also the page in the doublewrite buffer is corrupt.
InnoDB: Cannot continue operation.
InnoDB: You can try to recover the database with the my.cnf
InnoDB: option:
InnoDB: innodb_force_recovery=6

One of the reason for this error is the use of multiple
storage engines, MyISAM or InnoDB

Check your /etc/my.cnf for any lines which highlight the use
of multiple storage engines.

Following can be an example :

innodb_force_recovery=4
default-storage-engine=MyISAM

The above configuration implies MyISAM is the default
storage engine, but another setting related to innoDB is
already given, which conflicts.

If your default storage engine is MyISAM, then
giving the following option in /etc/my.cnf would
help : skip-innodb

Drupal 7 issue with SQL Mode TRADITIONAL

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable ‘sql_mode’ can’t be set to the value of ‘TRADITIONAL’ in lock_may_be_available() (line 165 of /includes/lock.inc).

This was the case when I installed Drupal 7 with Cpanel/Fantastico, the drupal site was displaying the above error.

This issue is discussed at drupal issues . try to patch it as mentioned in the url.

But for me it works with the following change, just removed the TRADITIONAL mode, not  sure it is the correct way to fix it. You can verify the sql modes at http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html ,  Anyway now there is no errors in drupal site and I am able to login.

(includes/database/mysql/database.inc) Line: 65

New file
<  $this->exec(“SET sql_mode=’ANSI,ONLY_FULL_GROUP_BY'”);

Old file
>  $this->exec(“SET sql_mode=’ANSI,TRADITIONAL'”);

Also setting up the sql connection mode to SET SESSION sql_mode = "ANSI,TRADITIONAL"; is an option instead of above change.

./arun

 

 


Mysql one way DB replication

One way replication of mysql database:

Mysql replication help us in keeping the data replicated to one or more sites reliably with binary logs. Apart from good amount of advantages Mysql replication doesn’t help with data corruption, since the corrupted data is replicated in all slaves. It is good to have periodic backup of database apart from replication.

Replication Steps

– Create database with same name on all servers
> mysql -u db_user -p -e "CREATE DATABASE db_name"

– Create database user with replication privilege on master
> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replication_clients" IDENTIFIED BY 'replication_password'
This can be also supplied with particular database name with ;db_name.*’ instead of *.*

– Edit Mysql master configuration (my.cnf) to allow replication
[mysqld]
server-id = 1 # Important with replication
log-slave-updates
log-bin = /var/lib/mysql/mysql-bin
log-bin-index = /var/lib/mysql/mysql-bin.index
replicate-do-db = db_name # specify the dbs to replicate
log-warnings
innodb_flush_log_at_trx_commit=1
sync-binlog=1
innodb_safe_binlog

– Take dump of master db and put them on all replicas

use db_name;
FLUSH TABLES WITH READ LOCK;

$ mysqldump -u dbuser -p db_name > db_dump.sql
install on slaves
$ mysql -u dbuser -p db_name < db_dump.sql


use db_name;
UNLOCK TABLES;

– Edit mysql configuration on replicas with master credentials

[mysqld]
old_passwords=1
server-id=2
innodb_file_per_table
log-slave-updates
master-host = master_hostname
master-port = master_port
master-user = master_user
master-password = master_password
log-bin = /var/lib/mysql/mysql-bin
log-bin-index = /var/lib/mysql/mysql-bin.index

Restart the Mysql daemon on all servers and check the replication status:
Master: > show master status;
Replicas: > show slave status;

Mysql backup script

To backup mysql on a daily/hourly basis with time stamp and compress it after backup also it will remove the files older than x days.

#!/bin/bash
# Arun N S
# variables
DATE="$(date +"%d-%m-%Y")"
TIME="$(date +"%d-%m-%Y-%H%M")"
USER=username
PASSWORD=password
DATABASE=dbname


# Directories and dump
/bin/mkdir -p /backup/Mysql/$DATE
/usr/bin/mysqldump -l -F -u $USER --password=$PASSWORD $DATABASE > /backup/Mysql/$DATE/backup_$TIME.sql


# Compressing
/usr/bin/bzip2 /backup/Mysql/*/*.sql


#Removing files older than x days eg: 90 days
for i in `/usr/bin/find /backup/Mysql/ -maxdepth 1 -type d -mtime +90 -print`; do
/bin/echo -e "Deleting old directories $i"; /bin/rm -rf $i; done