CentOs mysql must know commands in command-line interface


When working on mysql command line interface it can be hectic due to the fact that some of the syntax that might work in normal php code or phpmyadmin might not work in CentOS. The fact is that syntax is the same but just have to add some extra spicies to it
Users and DBA’s (Database Admins) create, delete, manipulate:
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”;
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;
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;
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;
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”;
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

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.