Reset MySQL root pwd

Resetting MySQL Root Password: Red Hat and CentOS

If you’ve forgotten your MySQL root password (or are involved with some nefarious activity), here is how you can reset that password on a Red Hat (RHEL) system or a CentOS system:
Stop MySQL:
root#  service mysqld stop
Start MySQL in safe mode:
root#  mysqld_safe –skip-grant-tables &
Log into MySQL as root:
root#  mysql -u root
Reset the password:
mysql>  update mysql.user set password=PASSWORD(“YourNewPassW0RD”) where User=’root’;
mysql>  flush privileges; exit;
Log out of MySQL and stop the Safe Mode:
root#  service mysqld stop
Start MySQL in the normal mode:
root#  service mysqld start
Log into MySQL with your new password:
root#  mysql -u root -p
Enter password:
mysql>

Use mysqladmin command to change root password

If you have never set a root password for MySQL server, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD
However, if you want to change (or update) a root password, then you need to use the following command:
$ mysqladmin -u root -p'oldpassword' password newpass
For example, If the old password is abc, you can set the new password to 123456, enter:
$ mysqladmin -u root -p'abc' password '123456'

Common MySQL Commands

Common Useful MySQL Commands

Applicable to Centos Versions:
  • Centos 5.x
  • Centos 6.x

Requirements

Explanation of requirements. 
  1. MySQL and MySQL-server installed on a Centos 5.x system
  2. DBA access rights to MySQL on a Centos 5.x system

Doing the Work

Basic description of what will be done and what is expected.
  1. Database file locations:
In Centos 5.x the location where the raw uncompressed MySQL databases are stored is: /var/lib/mysql
  1. Users and DBA’s (Database Admins) create, delete, manipulate:
Note: To create, delete or alter a DBA you'll first need to be logged in as the root mysql DBA or a DBA with full access.

Show all users in the mysql database:
mysql> SELECT * FROM mysql.user;

Delete a null user:
mysql> DELETE FROM mysql.user WHERE user = ' ';

Delete all users who are not root:
mysql> DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");

Change root user to a less-guessable name (for security):
mysql> UPDATE mysql.user SET user="mydbadmin" WHERE user="root";

Create a new DBA:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Add a new user with specific privileges on a specific database:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';

Add a new DBA with all privileges to a specific database:
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Change a user or DBA password:
mysql> UPDATE mysql.user SET password=oldpass("newpass") WHERE User='username';

Delete a user or DBA from the database:
mysql> DELETE FROM mysql.user WHERE user="username";
  1. Simple database manipulation:
Show all databases:
mysql> SHOW DATABASES;

Create a database:
mysql> CREATE DATABASE mydatabase;

Use a specific database:
mysql> USE mydatabase;

Delete a database:
mysql> DROP DATABASE mydatabase;

Optimize a database:
All Databases:
$ sudo mysqlcheck -o --all-databases -u root -p
Single Database:
$ sudo mysqlcheck -o db_schema_name -u root -p

Rename a database from command line (not from within MySQL), there is purposely no space beween -p and mypass:

mysqldump -u dbauser -pmypass databasename > mybackup.sql

mysql -u dbauser -pmypass newdatabasename < mybackup.sql

Show the last 200 queries to your database with the sample table name "queries" and the sample field "query_id":

mysql> SELECT * FROM queries ORDER BY query_id DESC LIMIT 200;
  1. Simple table manipulation:
Show all tables within a previously selected database 
mysql> SHOW TABLES;

Show data within a specific table in a previously selected database:
mysql> SELECT * FROM tablename;

Rename a table within a specific database:
mysql> RENAME TABLE first TO second;
or
mysql> ALTER TABLE mytable rename as mynewtable;

Drop a table from a specific database:
mysql> DROP TABLE mytable;
  1. Simple column and field manipulation:
Show columns within a table:
mysql> DESC mytable;
or
mysql> SHOW COLUMNS FROM mytable;

Update a field within a previously selected database table:
mysql> UPDATE mytable SET mycolumn="newinfo" WHERE mycolumn="oldinfo";

Select data within a specific table in a previously selected database:
mysql> SELECT * FROM mytable WHERE mycolumn='mydata' ORDER BY mycolumn2;

Insert data into a table and its columns within a specific database:
mysql> INSERT INTO mytable VALUES('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');

Delete data from specific fields within a column:
mysql> DELETE FROM mytable WHERE mycolumn="mydata";

Update data in a specific table, column, field:
mysql> UPDATE mytable SET column1="mydata" WHERE column2="mydata";
  1. Simple backup options:
Backup all databases uncompressed from the command line (not from within MySQL):
backup: mysqldump -u root -pmypass --all-databases > alldatabases.sql

restore full: mysql -u username -pmypass < alldatabases.sql (no space in between -p and mypass)

restore single: mysql -u username -pmypass mydb < mydb.sql (no space in between -p and mypass)

Backup all databases compressed from the command line (not from within MySQL):
With bzip2: mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 (use bunzip2 to uncompress)

With gzip: mysqldump --all-databases | gzip > databasebackup.sql.gz (use gunzip to uncompress)

Mini script to backup all databases and tables using gzip:
#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /var/backup/dbbackup-$date.sql.gz

Backup a specific database only:
mysqldump -u username -pmypass databasename > backupfile.sql

Backup database structure only:
mysqldump --no-data --databases databasename > structurebackup.sql


Backup a specific database and specific tables within that database only:
mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2 > databasebackup.sql

The syntax for the command to issue is:
mysqldump -u [username] -p[password] [databasename] [table1 table2 ....] > backupfilename.sql

Backing up only the database structure of specific databases, not the actual data:
mysqldump --no-data --databases db1 db2 db3 > structurebackup.sql
  1. Improve local and remote access security:
Disable the use of LOAD DATA LOCAL INFILE command, which will help to prevent against unauthorized reading from local files. This matters especially when new SQL Injection vulnerabilities in PHP applications are found. This can be set to 1 temporarily for a local admin to import a csv file into the database and then turned off again as well. The mysqld service will need to be restarted after each change.

For that purpose, the following parameter should be added in the [mysqld] section in /etc/my.cnf:

set-variable=local-infile=0

_____________________

The first change applies to the 3306/tcp port, on which MySQL listens by default. Because, according to the initial assumptions, the database will be used only by locally installed PHP applications, we can freely disable listening on that port. This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts. Local communication will be still possible throw the mysql.sock socket.

In order to disable listening on the mentioned port, the following parameter should be added to the [mysqld] section of /etc/my.cnf:

skip-networking

SSH Tunneling can be used for remote backup scripts which require access to the machine.

Troubleshooting How to test

Explanation of troubleshooting basics and expectations. 
  1. Make sure mysql and mysql-server are installed:
rpm -q mysql mysql-server
  1. Set mysqld to start on boot in runlevels 2,3,4, and 5, start the service and verify it’s running (you’ll need root privileges for this):
chkconfig --level 2345 mysqld on; service mysqld start; service mysqld status

Common problems and fixes

Common problems described here, includes links to known common problems if located on another site

Change MySQL root PWD

Use mysqladmin command to change root password
If you have never set a root password for MySQL server, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:
$ mysqladmin -u root password NEWPASSWORD
However, if you want to change (or update) a root password, then you need to use the following command:
$ mysqladmin -u root -p'oldpassword' password newpass
For example, If the old password is abc, you can set the new password to 123456, enter:
$ mysqladmin -u root -p'abc' password '123456'