MySQL (/maɪ ˌɛskjuːˈɛl/ “My S-Q-L”, officially, sometimes erroneously called /maɪ ˈsiːkwəl/ “My Sequel”) is (as of July 2013) the world’s second most widely used relational database management system (RDBMS) and most widely used open-source RDBMS. It is named after co-founder Michael Widenius‘s daughter, My. The SQL acronym stands for Structured Query Language. The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation. For proprietary use, several paid editions are available, and offer additional functionality.
MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web application software stack (and other ‘AMP’ stacks). LAMP is an acronym for “Linux, Apache, MySQL, Perl/PHP/Python.” Free-software-open source projects that require a full-featured database management system often use MySQL. Applications that use the MySQL database include: TYPO3, MODx, Joomla, WordPress, phpBB, MyBB, Drupal and other software. MySQL is also used in many high-profile, large-scale websites, including Google(though not for searches), Facebook, Twitter, Flickr, and YouTube.
install mysql port (3306)
#sudo apt-get install mysql-server
commands in CUI
All MySQL commands end with a semicolon;
To start mysql enter the username
#mysql -u root -p you have enter the password
Note :if mysql -uroot -p(password) direct will enter in mysql . but visible the password text .
create a database
create database <db_name>; or
with out log in to the mysql
#mysqladmin -u root -p create <db_name> (it will ask for the password)
create an user and password
CREATE USER 'ami'@'localhost';
CREATE USER 'ami'@'localhost' IDENTIFIED BY 'admin';
apply grant privileges for an user
mysql>grant index, create, select, insert, update, delete, alter, lock tables on <db_name>.* to ami@localhost; or
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
View users list
mysql>SELECT User FROM mysql.user;
Delete an user
mysql>DROP USER 'ami'@'localhost';
Del a db
mysql>drop database dbname;
Select a db
mysql>quit or exit
backup: # mysqldump -u root -p [database_name] > dumpfilename.sql restore:# mysql -u root -p [database_name] < dumpfilename.sql Note : These back up/restore will work on when u exit in mysql.
Backup all the databases:
mysqldump -u root -p --all-databases > /tmp/all-database.sql
Backuping two dbs
mysqldump -u root -p --databases <backup1> <backup2> > backup1andbackup2.sql
daily backup with crontab -e
add this entry 11 o clock morning daily it will backup
00 11 * * *mysqldump -u<username> -p<password> dbname > /home/usr/backupdb.sql
# mysqladmin -u root -p status
mysql -V will get you the version of the client.
mysqld -V will get you the version of the server.
vim /etc/mysql/my.cnf (we can change default port 3306 to any port )
sudo service mysql /start/stop/restart or
sudo /etc/init.d/mysql /start/stop/restart
Trouble shoot to getting error in log in to mysql
pkill -9 mysqld After you do this you might want to look for a pid file in
/var/run/mysqld/and delete it
chmod 777 /var/run/mysqld/mysqld.sock