Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

I got the below error while restarting mysql service after updating the my.cnf file :

 [ERROR] Plugin ‘InnoDB’ init function returned error.
 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
 [ERROR] Unknown/unsupported storage engine: INNODB
 [ERROR] Aborting 

Just followed the below steps to resolve the issue :

cd /var/lib/mysql
mv ibdata1 ib_logfile0 ib_logfile1 /tmp/

Note that my datadir was /var/lib/mysql. Change the location accordingly.

service mysqld restart

[root@theg-01 mysql]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Thats it 🙂

MySQL Master-Master Replication (below 5.5)

Follow the below steps to configure MySQL Master-Master replication (version < 5.5) between two servers :

I am using two servers with the following IP address :

Master 1 IP : 0.0.0.1
Master 2 IP : 0.0.0.2

STEP 1 :

Configure both the server’s configuration files under [mysqld] section :

*****In Master1 ******

Add the below lines to the my.cnf file :

bind-address = 0.0.0.0

server-id = 1

replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=1

master-host = 0.0.0.2
master-user = slaveuser
master-password = mycomplexpassword
master-connect-retry = 60

log-bin=/var/lib/mysqllogs/bin-log
expire_logs_days = 10
max_binlog_size = 100M

relay-log=/var/lib/mysqllogs/relay-log
relay-log-index=/var/lib/mysqllogs/relay-log.index

*****In Master2 **********

Add the below lines to the my.cnf file :

bind-address = 0.0.0.0

server-id = 2

replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=2

master-host = 0.0.0.1
master-user = slaveuser
master-password = mycomplexpassword
master-connect-retry = 60

log-bin=/var/lib/mysqllogs/bin-log
expire_logs_days = 10
max_binlog_size = 100M

relay-log=/var/lib/mysqllogs/relay-log
relay-log-index=/var/lib/mysqllogs/relay-log.index

STEP 2 :

Restart the mysql service on both the serves :

root@test1:~# service mysql restart
root@test2:~# service mysql restart

Points :

Make sure that the directory /var/lib/mysqllog exists with mysql:mysql permission
Check more errors in /var/log/mysql/error.log

STEP3 :

Configure the slave user in Master 1. Login to the mysql console in the Master 1 and run the below command :

root@test1:~# mysql -u root -p

mysql> GRANT replication slave ON *.* TO slaveuser@’0.0.0.2′ IDENTIFIED BY ‘mycomplexpassword’;

Take mysql backup in the master1 :

root@test1:~# mysqldump -u root -p –all-databases –master-data > ~/primary.sql

Move the backup to the master2 :

root@test1:~#scp -p 22 ~/primary.sql root@0.0.0.2:/root

STEP4 :

On Master2, restore the mysql backup which have been taken from Master1

Note : Stop the slave service in Master2 before restoring the backup.
root@test2:~# mysql -u root -p

mysql> GRANT replication slave ON *.* TO slaveuser@’0.0.0.1′ IDENTIFIED BY ‘mycomplexpassword’;

mysql> stop slave;
root@test2:~# mysql < /root/primary.sql

Now check the master’s log file and it’s position. You can check it from the mysqldump file itself.

root@test2:~# cat primary.sql | grep ‘CHANGE MASTER TO MASTER_LOG_FILE’ | more

Note down the fields MASTER_LOG_FILE= and MASTER_LOG_POS= , those contains the Master1 status. Instruct the slave to start with the fields we are already noted down.

Login to the mysql console in Master2

root@test2:~# mysql -u root -p

mysql> CHANGE MASTER TO master_host=’0.0.0.1′, master_port=3306, master_user=’slaveuser’, master_password=’mycomplexpassword’, MASTER_LOG_FILE=’bin-log.000001′,

MASTER_LOG_POS=25427;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

mysql>SHOW MASTER STATUS\G

Note down the MASTER_LOG_FILE= and MASTER_LOG_POS= from the Master2 status which we need to enter in the Master1 while starting the slave.

STEP5 :

On Master1, Start the client with the Master2’s status :

root@test1:~# mysql -u root -p

mysql> slave stop;

mysql> CHANGE MASTER TO master_host = ‘0.0.0.2’, master_port=3306, master_user=’slaveuser’, master_password=’mycomplexpassword’, master_log_file=’bin-log.000001′,

master_log_pos=10363106;

mysql> start slave;

mysql> SHOW SLAVE STATUS\G

Now test the replication by creating a sample database in server1. Check whether the database is replicated to the second server. If it

is, try to drop it in master2 and confirm that the replicated database is removed from the Master1 server.

To create database in master1

mysql> create database theG;

To Remove the database in master2

mysql> drop database theG;

Enjoy !!! 🙂

MySQL

MySQL Master-Master Replication

For MySQL servers above 5.5, use the below method :

Suppose servers are

server1=0.0.0.1
Server2=0.0.0.2

In Server1, change the /etc/mysql/my.cnf

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
#bind-address           = 127.0.0.1

Server2, change the /etc/mysql/my.cnf

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
#bind-address           = 127.0.0.1

In server1,

mysql> CREATE USER 'slave1'@'0.0.0.2' IDENTIFIED BY 'mypassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'0.0.0.2';

FLUSH TABLES WITH READ LOCK;

mysqldump -u root -p --all-databases --master-data > /data_dump.sql
show master status\G

note down the master values that we need to enter in the slave.

copy the bakcup to server2 and run

CHANGE MASTER TO   MASTER_HOST='0.0.0.1',   MASTER_USER='slave1',   MASTER_PASSWORD='mypassword',   MASTER_PORT=3306,   MASTER_LOG_FILE='mysql-bin.000002',   MASTER_LOG_POS=107;

start slave;

In server1,

unlock tables;

In server2,

mysql> CREATE USER 'slave2'@'0.0.0.1' IDENTIFIED BY 'mypassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'0.0.0.1';

FLUSH TABLES WITH READ LOCK;

show master status\G

Note down the master values that we need to enter in the next command

In server1,

mysql> CHANGE MASTER TO   MASTER_HOST='0.0.0.2',   MASTER_USER='slave2',   MASTER_PASSWORD='mypassword',   MASTER_PORT=3306,   MASTER_LOG_FILE='mysql-bin.000001',   MASTER_LOG_POS=537678;

In server2,

unlock tables;

Now create a database in server1 and check whether it is replicated in server2. If so, drop the database from server2 and confirm that it is removed from the server1 as well.

To create test database :

create database gtest;

To create test table :

CREATE TABLE gtest(id INT, name VARCHAR(20), email VARCHAR(20));

To Delete database :

drop database gtest;

To delete table

drop table gtest

Thanks 🙂

Bash script to take Mysqldump hourly and daily

Create a script as below and make it executable:

#!/bin/bash
Now_hourly=$(date +%d-%b-%H_%M)
Now_daily=$(date +%d-%b-daily)
if [ "$1" == "hourly" ]; then
/usr/bin/mysqldump --all-databases | gzip -c | cat > /Backup_location/mysql_dump_all$Now_hourly.sql.gz
elif [ "$1" == "daily" ]; then
/usr/bin/mysqldump --all-databases | gzip -c | cat > /Backup_location/mysql_dump_all$Now_daily.sql.gz
else
echo "Error. Enter hourly or daily";
fi

Now add the script in crontab for the hours it should run.

15,30 1,3 * * * script_name hourly
10,20 2,4 * * * script_name daily

Hourly backup will run at 01:15,01:30,03:15 and 03:30

Daily backup will run at 02:10,02:20,04:10 and 04:20 (since name would be same, it will replace each daily backup before)

Reset WordPress password from the command line

You can use the below steps to change the WordPress login :

1. Login to the mysql :

mysql -u root -p;

2. Select the database :

use database_name;

3. List the Users :

select * from wp_users\G

Note : Note down the password before you change. Just in case your client want the same password :p

4. Change the password for the user you required :

UPDATE wp_users SET user_pass=MD5('new_password') where ID=id_you_need;

Enjoy !!! 🙂 🙂 🙂