Category: Mysql

Categories
Linux Mysql

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

 

 


Categories
Linux Mysql

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;

Categories
Linux Mysql shell script

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