MYSQL

MYSQL

Main Configuration file is in /etc/my.cnf

Password stored in /root/.my.cnf

Service Start and stop
/etc/rc.d/init.d/mysqld start/stop/restart

How to Reset Root Password
ISSUE:- [root@tech23 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
1. service mysql stop
2. enter mysql command prompt using mysqld_safe --skip-grant-tables $
3. mysql>UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
4. Quit from Mysql and run the command " service mysql restart "

[root@tech23 ~]# service mysqld stop
[root@tech23 ~]# service mysqld status
mysqld is stopped
[root@tech23 ~]# mysqld_safe --skip-grant-tables &
[2] 18962
[root@tech23 ~]# 110222 17:19:53 mysqld_safe Logging to '/var/log/mysqld.log'.
110222 17:19:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@tech23 ~]# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("ajeesh-your new password") where User='root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 4 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

Thats all your


GRANT PRIVILEGES

mysql> GRANT ALL PRIVILEGES ON DBname.* TO user@ IDENTIFIED BY 'password';

mysql> flush privileges;
What all privileges that user has got?
mysql> show grants for user@localhost;

How to Find Out MYSQL Users
1. Enter into the Mysql database
2. Mysql> use mysql;
3. mysql> select distinct user from user;

If Any Table In the Databases are Corrupted
1. cd /var/lib/mysql/
2. From there run the command myisamchk -r *.MYI

Max_connection
mysql> show variables like "%max_connection%";
OR
in linux command prompt
#mysqladmin -p'password' ex|grep Threads_connected


DO SECTION

MYSQL Database Bachup
1. $ mysqldump -h host -u user -p database > backup.sql --> to create the backup
2. mysql -h host -u user -p -D database < backup.sql --> to restore the database from backup

Increase the No Of Mysql Connection
mysql> show processlist;
#vi /etc/my.cnf
max_connections= increase the no
# service mysql restart






ERROR SECTION

A Mysql Is Not Getting Started
1. vi /etc/my.cnf add line ' innodb_force_recovery = 4 '
2. comment out skip-innodb like #skip-innodb
3. Restart

ADDING REMOTE MYSQL CONNECTIONS
1. Go to WHM Main >> SQL Services >> Additional MySQL Access Hosts, Here we have add the IP address which we needs to access the database
2. Go to Cpanel under 'mysql' section, click on 'remote mysql' and Type that Remote Host IP

MYSQL DROP , DELETE , TRUNCATE TABLEs (01-12-2010)
Differences
DROP -> Delete the table from your database
Syntax : drop table friends(table name);

TRUNCATE -> Delete the contents from the table and if you have any auto increment it will reset to 0(from start again). It is very safe command for deleting contents from a table.
Syntax :
mysql> truncate table friends;
Query OK, 0 rows affected (0.00 sec)

DELETE -> Delete all the contents from table and will not reset your auto increment to 0 it will start again from the next value
Syntax : delete from friends(table name);