Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

Problems:

130702 12:48:19 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
130702 12:48:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Solution:

Step 1   Change “datadir” option to new directory i.e. /abcdb

           # vi /etc/my.cnf

[mysqld]

datadir=/abcdb

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

Step 2   Change ownership “mysql” of “/abcdb” directory.

Step 3   Disable selinux

 Step 4   Run below command:

                #mysql_install_db -user=mysql -ldata=/abcdb

Step 5   Restart the mysqld service

 

 

Last_Errno: 1007: Error 'can’t create database 'databasename'; database exists' on query. Default database: 'databasename'. Query: 'create database databasename'

1         Check below entry in slave server:

            mysql> show slave status\G;

Master_Log_File: mysql-bin.000006

            Read_Master_Log_Pos: 231202

                        Relay_Log_File: mysqld-relay-bin.000004

                        Relay_Log_Pos: 503

            Relay_Master_Log_File: mysql-bin.000003

           Slave_IO_Running: Yes

            Slave_SQL_Running: No

           

Note: 

a)      Both Master_Log_File and Relay_Master_Log_File must be same.

b)      Both Slave_IO_Running and Slave_SQL_Running must be yes.

2          Check file name and bin log position on master server:

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000006 |   231202 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

3.         Stop the slave server:

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

4.         Change master log file name and log position

mysql> CHANGE MASTER TO MASTER_HOST='xxxxx.xxx.xx',MASTER_USER='master_user',MASTER_PASSWORD='master_user_password',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=202;

Query OK, 0 rows affected (0.05 sec)

5.         Start Slave Server:

mysql> start slave ;

Query OK, 0 rows affected (0.01 sec)

6.         Check slave server status:

mysql> show slave status\G;

           Master_Log_File: mysql-bin.000006

            Read_Master_Log_Pos: 231202

                        Relay_Log_File: mysqld-relay-bin.000002

                        Relay_Log_Pos: 235

            Relay_Master_Log_File: mysql-bin.000006

            Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

            

MySQL- Lost / forgotten root password reset on Linux

Step 1: Stop MySQL daemon if it is currently running

Locate the .pid file that contains the server's process ID

Common locations are /var/lib/mysql/, /var/run/mysqld/ and /usr/local/mysql/data/.

Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.

or 

Check running process

# ps -ef | grep mysql     

Kills the daemon, if it is running.

# pkill mysqld  

or

# kill -9 /path/to/pid

Step 2: Run MySQL safe daemon with skipping grant tables

 # mysqld_safe --skip-grant-tables &

Step 3: Login to MySQL as root with no password

# mysql -u root mysql

Step 4:  Run UPDATE query to reset the root password

mysql> UPDATE user SET password=PASSWORD("NewPassword") WHERE user="root";

mysql> FLUSH PRIVILEGES;

Step 5:  Stop MySQL safe daemon

Note: This time only run kill (pkill) “mysqld_safe” instead of “mysqld”
               
# pkill mysqld_safe

Step 6:  Start MySQL daemon

# service mysqld start

Step 7: Root password is reset and ready to use

 # mysql -u root -p mysql

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

For resolving the problems follow below steps:

1.       Stop & Reset slave

mysql> stop slave;

mysql> reset slave;

2.       Change log position & master log file name

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.0000xx', MASTER_LOG_POS=xxxx;

3.       Start slave

mysql> start slave;

4.       Check slave status

mysql> show slave status\G;

Output include below output:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Error 'Duplicate entry '2619' for key 1' on query in MySQL

For resolving above error run below command on MySql Slave Server:

mysql> stop slave;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

Then, check Slave status:

mysql> show slave status\G;

Now “Slave_IO_Running” and “Slave_SQL_Running” will be “Yes”.

MySQL- Miscellaneous Examples (1)

1.             To create user “databaseuser” on host “databasehost” with password “password”:

mysql> CREATE USER 'databaseuser'@' databasehost ' IDENTIFIED BY 'password';

2.             To create database “databasename”:

mysql> CREATE DATABASE datbasename;

3.             To display databases:

mysql> SHOW DATABASES;

4.             To display tables:

mysql> SHOW TABLES;

5.             To change database;

mysql> USE datbasename;

6.             To delete database:

mysql> DROP DATABASE databasename;

7.             To grant permission on database “databasename”:

mysql> GRANT ALL ON databasename.* TO 'databaseuser'@' databasehost ';

8.             To show grant privileges:

mysql> SHOW GRANTS FOR  'databaseuser'@' databasehost ';

9.             To remove grant privileges:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'databaseuser'@' databasehost ';

10.          To make change in effect:

mysql> FLUSH PRIVILEGES;

11.          To update user table:

mysql> UPDATE user SET host='newhostname' WHERE host='oldhostname';               

12.          To show user table:

mysql> SELECT * FROM user;

13.          To reset user password:

mysql> UPDATE user SET Password=PASSWORD('password') WHERE user="'databaseuser'@' databasehost’";

14.          To rename root user:

mysql> UPDATE USER SET user="newdatabaseadmin" WHERE user="root";

15.          To remove entry from user table:

mysql> DELETE FROM user WHERE host="databasehost" AND user="databaseuser”;

16.          To show db tables:

mysql> SHOW * FROM db;

17.          To update db tables

mysql> UPDATE db SET host='newdatabasehost' WHERE db="olddatabasehost";

18.          To remove entry from db table:

mysql> DELETE FROM db WHERE db="databaseuser";

19.          To take database backup:

# mysqldump –h hostname –u username –p databasename > /path/to/backup/filename.sql

20.          To restore database backup:

# mysql –h hostname –u username –p databasename < /path/from/restore/filename.sql