MySQL

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

mysql>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';

DROP USER user;

save privileges

mysql>flush privileges;

show db

mysql>show databases;

Del a db

mysql>drop database dbname;

Select a db

mysql>use dbname;

Exit

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

Status

# mysqladmin -u root -p status

Version

mysql -V will get you the version of the client.

mysqld -V will get you the version of the server.

Configuration File

vim /etc/mysql/my.cnf  (we can change default port 3306 to any port )

Start/Stop/Restart

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