—Red Hat / Fedora Core RPM Packages:

mysql-version.rpm (Required)
mysql-server-version.rpm (Required)
mysqlclient9-version.rpm (Shared object libraries)
mysql-devel-version.rpm (C include files and libraries for developers)
php-mysql-version.rpm (For accessing MySQL database from php)

–Install: rpm -ivh mysql-version.rpm mysql-server-version.rpm mysqlclient9-version.rpm

—Check if installed: rpm -q mysql mysql-server mysqlclient9

—[Potential Pitfall]: Your system should have a host name other than the default “localhost”.
—Give your systems a host name if you get the following installation error:

ERROR: 1062 Duplicate entry ‘localhost-root’ for key 1
ERROR: 1062 Duplicate entry ‘localhost-‘ for key 1

Use the command hostname to give your system a hostname and also set in the configuration file /etc/sysconfig/network

Ubuntu / Debian package installation:

apt-get install mysql-client
apt-get install mysql-server

Start the database:

Start the database: /etc/rc.d/init.d/mysqld start
(The script will run /usr/bin/mysql_install_db to create a default database in /var/lib/mysql/mysql/ if the mysql init script has never been run before. The install script will not be run again as long as the default database directory exists.)
The database executes as user mysqld and group mysqld.

Notes:

Init script will create and initialize the database with the command: /usr/bin/mysql_install_db
Creates system tables in /var/lib/mysql/mysql/
Only executes the first time the MySQL init script is run. One may also initialize the database manually using this command.
Files for databases located in: /var/lib/mysql/
Default config file installed by RPM: /etc/my.cnf
(Ubuntu: /etc/mysql/my.cnf)

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Post installation:

Admin user id: root
Default password: blankThe first task is to assign a password:

[prompt]$ mysqladmin -u root password ‘new-password’

Note: the following SQL commands will also work:

mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD(‘new-password’) WHERE user=’root’;
mysql> FLUSH PRIVILEGES;

Create a database: (Creates directory /var/lib/mysql/bedrock)

[prompt]$ mysqladmin -h localhost -u root -ppassword create bedrock

(or use SQL command: CREATE DATABASE bedrock;)
Show all mysql databases: mysqlshow -u root -ppassword

Add tables, data, etc:
Connect to database and issue the following SQL commands:

[prompt]$ mysql -h localhost -u root -ppassword
…
mysql> show databases; — List all databases in MySQL.
+———-+
| Database |
+———-+
| bedrock |
| mysql |
| test |
+———-+
mysql> use bedrock; — Specify database to connect to. Also refers to path: /var/lib/mysql/bedrock
mysql> create table employee (Name char(20),Dept char(20),jobTitle char(20));
mysql> DESCRIBE employee; — View table just created. Same as “show columns from employee;”
+———-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———-+——+—–+———+——-+
| Name | char(20) | YES | | NULL | |
| Dept | char(20) | YES | | NULL | |
| jobTitle | char(20) | YES | | NULL | |
+———-+———-+——+—–+———+——-+
3 rows in set (0.03 sec)

mysql> show tables;
+——————-+
| Tables_in_bedrock |
+——————-+
| employee |
+——————-+

mysql> INSERT INTO employee VALUES (‘Fred Flinstone’,’Quarry Worker’,’Rock Digger’);
mysql> INSERT INTO employee VALUES (‘Wilma Flinstone’,’Finance’,’Analyst’);
mysql> INSERT into employee values (‘Barney Rubble’,’Sales’,’Neighbor’);
mysql> INSERT INTO employee VALUES (‘Betty Rubble’,’IT’,’Neighbor’);

Note: Data type used was CHAR. Other data types include:
CHAR(M) : Fixed length string. Always stores M characters whether it is holding 2 or 20 characters. Where M can range 1 to 255 characters.
VARCHAR(M) : Variable length. Stores only the string. If M is defined to be 200 but the string is 20 characters long, only 20 characters are stored. Slower than CHAR.
INT : Ranging from -2147483648 to 2147483647 or unsigned 0 to 4294967295
FLOAT(M,N) : FLOAT(4,2) – Four digits total of which 2 are after the decimal. i.e. 12.34 Values are rounded to fit format if they are too large.
DATE, TEXT, BLOB, SET, ENUM
Add a user. Use the MySQL SQL console to enter SQL commands. The command mysql with the correct login/password will connect you to the database. The admin tables are stored in the database “mysql”.

[prompt]$ mysql -h localhost -u root -ppassword
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.41

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> USE mysql;
mysql> SHOW TABLES;
+—————–+
| Tables_in_mysql |
+—————–+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+—————–+
mysql> INSERT INTO user (Host, User, Password, Select_priv)
VALUES (”, ‘Dude1’, password(‘supersecret’), ‘Y’);
mysql> FLUSH PRIVILEGES; — Required each time one makes a change to the GRANT table
mysql> GRANT ALL PRIVILEGES ON bedrock.* TO Dude1;
mysql> FLUSH PRIVILEGES; — Required each time one makes a change to the GRANT table
mysql> quit

Note:
There is NO space between the -p and the password! You can omit the password and you will be prompted for it.
The SQL flush command is equivalent to issuing the command:

[prompt]$ mysqladmin reload

Test the database:

mysql> SELECT * from employee;
+—————–+—————+————-+
| Name | Dept | jobTitle |
+—————–+—————+————-+
| Fred Flinstone | Quarry Worker | Rock Digger |
| Wilma Flinstone | Finance | Analyst |
| Barney Rubble | Sales | Neighbor |
| Betty Rubble | IT | Neighbor |
+—————–+—————+————-+
1 row in set (0.00 sec)

mysql> SELECT name FROM employee WHERE dept=’Sales’;
+—————+
| name |
+—————+
| Barney Rubble |
+—————+
1 row in set (0.00 sec)

Quit from the SQL shell:

[prompt]$ quit

Shutting down the database:

[prompt]$ mysqladmin -u root -ppassword shutdown – PREFERRED
OR
[prompt]$ /etc/rc.d/init.d/mysqld stop
OR
[prompt]$ service mysqld stop

Documentation in /usr/share/doc/mysql-3.23.41/ (local file)
Users and Security:
Create a database user:

mysql> CREATE USER david@’localhost’ IDENTIFIED BY ‘password’;

or generate a user by adding them to the user table directly:

[prompt]$ mysql -h localhost -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘localhost’,’david’,PASSWORD(‘supersecret’));

Note that the user is defined in the “user” mysql table.

Assign user privileges:

Security and database access is controlled by the GRANT tables. Access to connect to the database and access to process the transaction (table and column access, etc.) are both required. Privileges are searched in the following order:

user table
db and host table
tables_priv
columns_priv

Use the user table to grant connection privileges to database by a user (host, user name and password). Grant database and table access for transaction access. i.e. grant “SELECT”, “UPDATE”, “CREATE”, “DELETE”, “ALTER” etc. permission for database, table, field (columns) or database server access.

Access can be granted by network permissions: GRANT ALL PRIVILEGES on bedrock.* to david@’192.168.10.0/255.255.255.0′;
This grants access from nodes 192.168.10.0 – 192.168.10.255. Or the network definitions can reference resolvable names: ‘%.domain.com‘. The host definition of ‘%‘ or ” (null) refers to any host. (..according to the documentation. My experience is that in the mysql.user table use only ‘%’ for “Host” to refer to any host.)

mysql> GRANT ALL PRIVILEGES on bedrock.* to david@’%’;
mysql> FLUSH PRIVILEGES;

or (be specific)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE on bedrock.* to david@’%’ identified by ‘david’;
mysql> FLUSH PRIVILEGES;

or (more promiscuous – global privileges rather than database specific)

mysql> GRANT ALL PRIVILEGES on *.* to david@’%’ identified by ‘david’;
mysql> FLUSH PRIVILEGES;

or (be specific by direct assignment in the mysql “Db” table:)

mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv) VALUES(‘localhost’,’bedrock’,’david’,’Y,’Y’);
mysql> FLUSH PRIVILEGES;

Note that database specific privileges (eg. Select_priv, Insert_priv, etc) are defined in the “db” mysql table. The mysql “user” table can assign the same (Select_priv, Insert_priv, etc) but global privileges (usually all default to ‘N’).Show privileges: SHOW GRANTS FOR Dude2@’%’;

Network security: Use firewall rules (ipchains or iptables) to block internet access to port 3306. (default port used by MySQL)

Note: I have found that when adding access from “anywhere” (‘%’), the MySQL database table ‘user’ requires two entries, ‘localhost’ and ‘%’. Also, it is typically safer to allow more privileges to those with ‘localhost’ access than to users from ‘%’ (“anywhere”).

Passwords and connecting to the databse:

Connect: [prompt]$ mysql -h host_name -u user_name -ppassword
Using default blank password: [prompt]$ mysql -h localhost -u root -p
If a password is required, you will be prompted. Note, blank passwords are a security hole which has already lead to one mySQL internet worm. Change any default blank passwords.
Delete null/blank users: DELETE FROM user WHERE User = ”;
Beware of open access permissions from hosts ‘%’: SELECT * FROM db WHERE Host = ‘%’;
Change a password:

[prompt]$ mysqladmin -u root -p password new-password

You will be prompted to enter the old root password to complete this command.
or:

[prompt]$ mysqladmin -u root -pold-password password new-password

or:

mysql> SET PASSWORD FOR root@’localhost’ = PASSWORD(‘supersecret’);
mysql> FLUSH PRIVILEGES;

As an added security precaution it is wise to delete any user id not used. i.e. any defaults generated for demonstration purposes.
Note that the default port used by MySQL is 3306. This can be protected with firewall rules. See the YoLinux IpTables tutorial.

Debian/Ubuntu upgrades: Note that the Debian/Ubuntu distribution will have an additional file /etc/mysql/debian.conf. This file holds a password for the user “debian-sys-maint” which is used by the install tool dpkg to perform database upgrades. This can also be used in emergencies if you forget the root password. It is also a security hole if the file is available to others.

[Potential Pitfall]: It is very easy to make mistakes which get entered into important tables. If you enter the command twice you may have one incorrect and one correct entry. Look at the table data after a mistake to see what happened in case it needs to be fixed.
Example:

mysql> USE mysql;
mysql> SELECT User,Password,Host from user;
+——-+——————+————+
| User | Password | Host |
+——-+——————+————+
| root | 99a1544eb571ad63 | localhost |
| | | localhost |
| Dude1 | 81a10dba5f6f2144 | |
| Dude1 | | |
| Dude2 | 92b10dba6f7f3155 | % |
+——-+——————+————+
5 rows in set (0.00 sec)
mysql> DELETE FROM user WHERE User=” AND Host=’localhost’;
mysql> DELETE FROM user WHERE User=’Dude1′ AND Password=”;
mysql> FLUSH PRIVILEGES;
mysql> QUIT

User entries may also be found in the table mysql.db.

mysql> DELETE FROM db WHERE User=’Dude3′ AND Host=’localhost’;

[Potential Pitfall]: Any changes (UPDATE) to the user table will require a “FLUSH PRIVILEGES” before the changes will be effective.

mysql> UPDATE user SET Host=’%’ WHERE User=’Dude2′;
mysql> FLUSH PRIVILEGES;

This will allow a connection with mysql client from any host:
[prompt]$ mysql -u Dude2 -ppassword -h node.your-domain.com

MySQL root password recovery:

As Linux system root user stop the database process: /etc/init.d/mysql stop
(or: service mysql stop)
Start MySQL in safe mode and skip the use of the “grant tables”: /usr/bin/mysqld_safe –user=mysql –socket=/var/lib/mysql/mysql.sock –pid-file=/var/run/mysqld/mysqld.pid –datadir=/var/lib/mysql –skip-grant-tables –skip-networking &
Reset the MySQL root password: mysqladmin -u root flush-privileges password newpassword
Stop MySQL running in safe mode: kill `cat /var/run/mysqld/mysqld.pid`
Start MySQL: /etc/init.d/mysql start
The new MySQL root password can now be used: mysql -u root -p
Respond with the password: newpassword

Disabling networking:

If your configuration is a web server interacting with a mySQL database running on the same “localhost” then one may turn off network access to tighten security. Edit shell script:

/usr/bin/safe_mysqld (Fedora Core 3)
/usr/bin/mysqld_safe (Red Hat Enterprise Linux 4 – MySQL 5.0)

..
…
if test -z “$args”
then
$NOHUP_NICENESS $ledir/$MYSQLD $defaults –basedir=$MY_BASEDIR_VERSION
–datadir=$DATADIR $USER_OPTION –pid-file=$pid_file
–skip-networking –skip-locking >> $err_log 2>&1
else
eval “$NOHUP_NICENESS $ledir/$MYSQLD $defaults –basedir=$MY_BASEDIR_VERSION
–datadir=$DATADIR $USER_OPTION –pid-file=$pid_file
–skip-networking –skip-locking $args >> $err_log 2>&1”
fi
…
..

Add the flag “–skip-networking” marked in bold.

Mysql 5.0 configuration: Networking is disabled by default on the default Red Hat and Ubuntu installation.

Red Hat/CentOS: To enable remote database access, add the “bind-address” with the public IP address to the file: /etc/my.cnf. To force local access only without remote access, set the “bind-address” to 127.0.0.1

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
bind-address=127.0.0.1

Restart the database after making changes.

Ubuntu: To enable remote database access, comment out (or remove) the following line with a “#” in the file: /etc/mysql/my.cnf

…
…

bind-address = 127.0.0.1

…
…

Restart the database after making changes.

A firewall rule can further restrict access to a single server (eg web server at 192.168.1.13):

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.13 -p tcp –destination-port 3306 -j ACCEPT

or LAN only access:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp –destination-port 3306 -j ACCEPT

MySQL Admin Commands:

Statistics: [prompt]$ mysqladmin version
List database environment: [prompt]$ mysqladmin variables
Show if database is running: [prompt]$ mysqladmin ping
Show databases available:

[prompt]$ mysqlshow

+———–+
| Databases |
+———–+
| bedrock |
| mysql |
| test |
+———–+

OR

mysql> SHOW DATABASES;

Delete database: mysql> drop database bedrock;
Show list of active threads in server:

[prompt]$ mysqladmin -h localhost -u root -p processlist

+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 15 | root | localhost | | Query | 0 | | show processlist |
+—-+——+———–+—-+———+——+——-+——————+

Delete a database: [prompt]$ mysqladmin drop database-name
Execute SQL from Linux command line interface:
[prompt]$ mysql -h localhost -u root -p -e “select host,db,user from db” mysql
Execute SQL command file from Linux command line interface:

[prompt]$ mysql -h localhost -u root -p database-name < text-file-with-sql-statements.sql

Loadtest (benchmark) the system:

[prompt]$ cd sql-bench
[prompt]$ run-all-tests
or
[prompt]$ mysql -vvf test < ./tests/auto_increment.tst

Sample SQL:

SQL requests are either administrative or data-related. The following are sample SQL segments and are not necessarily pertinent to the previous example:

Create and use a new database named “bedrock”:

mysql> CREATE DATABASE bedrock; — Comments follow a double dash
mysql> USE bedrock;

Create and populate table with data:

mysql> CREATE TABLE retired_employee (
Name char(20) DEFAULT ” NOT NULL,
Dept char(10) DEFAULT ” NOT NULL,
JobTitle char(20),
UNIQUE name_dept (Name,Dept)
);
mysql> CREATE UNIQUE index name_dept on employee (name,dept); — avoids duplicate keys
mysql> INSERT INTO employee VALUES (“Jane Smith”,”Sales”,”Customer Rep”);
mysql> INSERT INTO employee VALUES (‘Jane Smith’,’Sales’,’Account Manager’);
mysql> INSERT INTO employee VALUES (‘Jane Smith’,’Engineerin’,’Manager’);
mysql> UPDATE employee SET dept=’HR’ WHERE name=’Jane Smith’;
mysql> CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE);

Add constraints to a table:

— Use “auto_increment” integer column:
mysql> ALTER TABLE employee ADD EmpId INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
mysql> ALTER TABLE employee DROP INDEX name_dept; — get rid of index
mysql>

Interrogate an existing database:

mysql> SHOW DATABASES;
mysql> USE bedrock;
mysql> SELECT DATABASE(); — returns current database. eg. bedrock
mysql> SELECT VERSION();
mysql> SELECT NOW();
mysql> SELECT USER();
mysql> SHOW TABLES;
mysql> DESC employee;
mysql> SHOW CREATE TABLE employee; — show command used to generate table
mysql> SHOW INDEX FROM employee;
mysql> SELECT DISTINCT dept FROM bedrock;
mysql> SELECT * FROM bedrock WHERE Name LIKE “B%y”; — “%” match any char: Gives Betty and Barney
mysql> SELECT * FROM bedrock WHERE Name LIKE “B___y”; — “_” match space: Gives Betty but not Barney
mysql> SELECT * FROM bedrock WHERE Name RLIKE “^Betty$”; — “^” match beginning. “$” to denote end of string
mysql> SELECT COUNT(*) FROM employee; — Number of records returned
mysql> SELECT Name, COUNT(*) FROM employee WHERE Name LIKE “B%y”; — Return Names and number of records returned
mysql> SELECT * FROM pet WHERE species = “snake” OR species = “bird”;
mysql> SELECT * FROM pet WHERE species = “dog” AND sex = “f”;
mysql> SELECT * FROM pet WHERE birth >= “1998-1-1”;
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT * FROM pet WHERE name LIKE “b%”;
mysql> SELECT * FROM pet WHERE name REGEXP “^b”;
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
mysql> SELECT MAX(article) AS article FROM shop;
mysql> SELECT * FROM employee WHERE name LIKE “%Sm%”;
mysql> SELECT * FROM employee WHERE name REGEXP “^Ja”;

Database cleanup:

mysql> DROP TABLE employee;
mysql> DROP DATABASE bedrock;

See section 3 of MySQL manual for more examples.

Tip: Execute a shell command from the MySQL client interface, use either option:

system ls -l
OR
! ls -l

Example: execute the “ls” command to list files from the MySQL client.
Loading Data into the MySQL database:
Loading a SQL file into MySQL:

Import SQL file from MySQL client command line:

mysql> source file.sql
OR
mysql> . file.sql

The SQL file may have schema generation statements like CREATE TABLE … or data load statements like INSERT INTO … . The statements in the SQL file will be executed as if they were being specified at the MySQL client command line interface.

One may import data into the MySQL database from SQL files or “load” data from CSV or tab delimited files using the LOAD command:
Loading CSV or tab delimeted files into MySQL:

“LOAD DATA LOCAL INFILE” vs “LOAD DATA INFILE“: The term “LOCAL” pertains to whether the file is local to the MySQL client. Without the keyword “LOCAL“, the datafile must reside on the same computer as the database server. The location of the client in this case would be irrelevant. The “LOAD DATA INFILE” has many file permission pitfalls and is thus trickey. In fact I have never been sucessful using this method with a user directory.
Load a tab delimited file into the database:

Command: LOAD DATA LOCAL INFILE ‘file.dat’ INTO TABLE employer;

Input tab delimited file: file.dat

Fred Flinstone Quarry Worker Rock Digger
Wilma Flinstone Finance Analyst
Barney Rubble Sales Neighbor
Betty Rubble IT Neighbor

Note:

The number of tab delimeted fields MUST match the number and order of fields in the database.

Load a comma delimited file (CSV) into the database:

Command: LOAD DATA LOCAL INFILE “/tmp/TableData.csv” INTO TABLE employer FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY “””” LINES TERMINATED BY “rn” (Name, Dept, jobTitle); Note:

MS/Windows generated files will have lines terminated by “rn”.
Linux/Unix generated files will have lines terminated by “n”.
File locations on database server must be absolute path names, relative path or relative to the mysqld process owner’s home directory (typically /var/lib/mysql/). File locations on the client may be fully qualified or relative to the current mysql client directory.
Fully qualified: /tmp/TableData.csv
Relative to current mysql client directory: ./TableData.csv
(Verify current directory: mysql> ! pwd)
Database process owner home directory: TableData.csv
(Actual: /var/lib/mysql/TableData.csv)
Text strings often are encapsulated by quotes so that the strings may contain a comma without representing a new field.

[Potential Pitfalls]:

ERROR 13 (HY000): Can’t get stat of ‘/tmp/TableData.csv’ (Errcode: 13)
The fils is local and you have not specified the “LOCAL” directive.
ERROR 29 (HY000): File ‘/var/lib/mysql/test/TableData.csv’ not found (Errcode: 2)
Error from command LOAD DATA INFILE ‘TableData.csv’ INTO … where the file is assumed to be read from the /database-process-home-directory/mysql-database-name/TableData.csv
(Note: Database name “test” is being used.)
ERROR 1045 (28000): Access denied for user ‘user1’@’%’ (using password: YES)
OR
ERROR 2 (HY000): File ‘/tmp/TableData.csv’ not found (Errcode: 2)
Error from command LOAD DATA INFILE ‘/tmp/TableData.csv’ INTO …. This is a common pitfall, trying to load a file located on the database server remotely from a client. Getting the file permissions correct is difficult. Avoid this method. Use the LOAD DATA LOCAL INFILE instead of the LOAD DATA INFILE method (it is so much easier).

Also look at the mysqlimport command.
Dump/Backup/Transfer Database:

The mysqldump command will read the mySQL database and generate a SQL command text file. This allows data to be migrated to other versions of mySQL (i.e. upgrade from typical Red Hat (RH7.x to FC3) mySQL release 3.23.58 to a more advanced mySQL 4.1 or 5.0) or to other SQL databases. SQL command file generated can create tables, insert data, ….

Option Description
-A
–all-databases Dump all the databases.
-B
–databases Dump the specified databases.
-h
–host= Specify host to connect to.
-p
–password= Specify password. If you do not specify a password, then you will be queried.
-u
–user= Specify user. Defaults to current user logged in.
–opt Same as: –add-drop-table –add-locks –all –extended-insert –quick –lock-tables
–add-drop-table Add a “drop table” SQL statement before each “create” SQL statement.
–add-locks Add “lock” SQL statements around “insert” SQL statements.
-a
–all Include all mySQL specific SQL “create” options.
-e
–extended-insert Allows utilization of the new, much faster INSERT syntax. Database you are migrating to must support this notation.
-q
–quick Don’t buffer query, dump directly to stdout.
-l
–lock-tables Lock all tables for read.
-?
–help Display command line options.

Examples:

Dump database to a file:
Dump specified database:
mysqldump –opt database > db-dump-file.sql
Dump specified table in database:
mysqldump –opt database table-name > db-dump-file.sql
Dump multiple databases:
mysqldump –opt –databases database1 database2 database3 > db-dump-file.sql
Dump everything:
mysqldump –opt –all-databases > total-db-dump-file.sql
mysqldump -u user-id -h host-name –opt –all-databases > total-db-dump-file.sql

[Potential Pitfall]: If you experience the following error:

mysqldump: Got error: 1016: Can’t open file: ‘Database-Name’ (errno: 145) when using LOCK TABLES

Fix with the following command: mysqlcheck -r -u root -p Database-Name
Import dumped file:
mysql database < db-dump-file.sql
Export from one database and import to another:
Transfer specifed database from one database to another:
mysqldump –opt database | mysql –host=host-name -C database

Man Page:

mysqldump

Upgrading to 4.1:

Upgrading mySQL to 4.1 from 3.23
Use the command: mysql_fix_privilege_tables –password=root-password
This allows you to use the new GRANT command.

Restore MySql Database:

Restore using dump generated by mysqldump above:

mysql -h host-name -u user-id -psupersecretpassword < total-db-dump-file.sql
mysql database-name -h host-name -u user-id -psupersecretpassword < db-dump-file.sql

System Notes:
Quiesce Database:

When using enterprise storage with “snapshot” capabilities (eg Netapp, HP, EMC, …), note that a “snapshot” of a database with transaction locks is a “snapshot” of a database in an inconsitant state. One should quiesce the database, to halt further changes, before taking the snapshot so that you have a transactionally consistent backup of mysql. Check Database Status:

Show process list (database activity):

mysql> SHOW PROCESSLIST;
+———+———–+———–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———+———–+———–+——+———+——+——-+——————+
| 1713762 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 1713939 | user4 | localhost | SF | Sleep | 0 | | NULL |
+———+———–+———–+——+———+——+——-+——————+
2 rows in set (0.00 sec)

Show the database status:

mysql> SHOW ENGINE INNODB STATUSG
*************************** 1. row ***************************
Status:
=====================================
130505 9:20:52 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 51 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 1084680, signal count 1069717
Mutex spin waits 0, rounds 1777219, OS waits 4374
RW-shared spins 2163653, OS waits 1078122; RW-excl spins 7932, OS waits 113
————
TRANSACTIONS
————
Trx id counter 0 2656185
Purge done for trx’s n:o < 0 2652578 undo n:o < 0 0
History list length 9
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0 0, not started, process no 17024, OS thread id 1092143424
MySQL thread id 1713762, query id 24061088 localhost root
SHOW ENGINE INNODB STATUS
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)

…
…

Flush table transactions and lock: Closes all open tables and locks all tables for all databases with a global read lock.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

This will temporary make MySQL read only. It also requires LOCK TABLES privileges.Database activity: One can then view database activity with the SQL command: SHOW PROCESSLIST;
If configured for replication in a master/slave cluster, the master node may have binary log files of changes for the slaves.
View status: SHOW MASTER STATUS/G
If backing up from a slave: SHOW SLAVE STATUS/G
Database activity should rapidly complete and when it has, this is the time to perform backups and snapshots.

XFS volume freeze: Note file system considerations (XFS): (after the “READ LOCK” has been applied and the database has been locked)
Freeze from further modification, an entire file system volume on which the MySQL data resides: SYSTEM xfs_freeze -f /var/lib/mysql;
The backlog of write activity will complete and further file system modification will be denied.
Perform backups now.
Unfreeze filesystem volume: SYSTEM xfs_freeze -u /var/lib/mysql;

Take database snapshot then unlock:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Leave a comment

MySQL : Users and Permissions
——————————-

This article provides a quick guide to creating users and managing permissions for those users in MySQL.

Create a New User
Modify a User
Drop a User
Manage Privileges
Roles
Display DDL for Users and Permissions

Shortcut Commands:
——————

\s —- The \s command returns the status of the current database connection
\r—– To force a reconnect to the database server, issue the \r command
\e—– command to open an editor containing the current query ,To execute the query again,
enter a semicolon or use the \g or \G command
\c command to clear the current command and return to the mysql> prompt
Related articles

MySQL : Configure SSL Connections

Create a New User

When creating a new user, the CREATE USER command expects both a username and host. If the host is not supplied, a host of ‘%’ is used,
meaning any host other than localhost. As a result, if you want to create new admin user on the database, you may do something like this.

CREATE USER ‘adminuser’@’localhost’ IDENTIFIED BY ‘MyPassword1’;
GRANT ALL PRIVILEGES ON *.* TO ‘adminuser’@’localhost’ WITH GRANT OPTION;
CREATE USER ‘adminuser’@’%’ IDENTIFIED BY ‘MyPassword1’;
GRANT ALL PRIVILEGES ON *.* TO ‘adminuser’@’%’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

To get a list of MySQL users:
mysql> select user,host from mysql.user;

mysql> show grants for ‘root’@’%’;

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

We have in fact created two users, one for local access from the server and one for remote access.

mysql> SELECT host, user FROM mysql.user WHERE user = ‘adminuser’;
+———–+———–+
| host | user |
+———–+———–+
| % | adminuser |
| localhost | adminuser |
+———–+———–+
2 rows in set (0.01 sec)

mysql>

Notice the FLUSH PRIVILEGES command, which reloads the privilege information from the relevant tables in the “mysql” schema.

Not surprisingly, if you want to lock down a user, so it can only be accessed from a single PC or server, specify that machine name or IP
address in the user creation.

CREATE USER ‘myuser’@’123.123.123.123’ IDENTIFIED BY ‘MyPassword1’;
FLUSH PRIVILEGES;

MySQL also allows you to insert directly into the “mysql.user” table, but it is better to stick with the main commands.

Another alternative is to create using the GRANT command. Using GRANT USAGE creates the user, but grants it no privileges.

GRANT USAGE ON *.* TO ‘myuser’@’%’ IDENTIFIED BY ‘MyPassword1’;
FLUSH PRIVILEGES;

Modify a User

There are several ways to modify an existing user. MySQL allows DML to be run directly on the “mysql.user” table, so you can make changes directly.
For example, when we have the same user name defined against multiple hosts, we can update the passwords for all those users in a single step.

UPDATE user SET password = PASSWORD(‘MyPassword2’) WHERE user = ‘adminuser’;
FLUSH PRIVILEGES;

Alternatively, the SET PASSWORD command can be used to reset a users password.

— Specified user.
SET PASSWORD FOR ‘adminuser’@’localhost’ = PASSWORD(‘MyPassword2’);
FLUSH PRIVILEGES;

— Current user.
SET PASSWORD = PASSWORD(‘MyPassword2’);
FLUSH PRIVILEGES;

The ALTER USER command can be used to expire a password.

ALTER USER ‘adminuser’@’localhost’ PASSWORD EXPIRE;
FLUSH PRIVILEGES;

The RENAME USER comment, as the name suggests, renames a user.

RENAME USER ‘adminuser’@’localhost’ TO ‘adminuser’@’127.0.0.1’;

Drop a User

Users are removed using the DROP USER command.

DROP USER ‘adminuser’@’%’;
FLUSH PRIVILEGES;

If you have the same user defined for multiple hosts, remember to drop all of them if required.

mysql> SELECT host, user FROM mysql.user WHERE user = ‘adminuser’;
+———–+———–+
| host | user |
+———–+———–+
| localhost | adminuser |
+———–+———–+
1 row in set (0.00 sec)

mysql>

Alternatively, just delete all users with the same user name.

DELETE FROM mysql.user WHERE user = ‘adminuser’;
FLUSH PRIVILEGES;

Manage Privileges

MySQL does not support roles in the same sense as Oracle roles. Some tools, like MySQL Workbench, allow you to model roles, but ultimately these are
implemented using direct grants on users.

The full syntax for GRANT and REVOKE are listed in the documentation, but the following examples will give you an idea of how the privileges can be
used at different levels. Notice how the same privilege can be used with a dramatically different results based on the scope of the grant.

— Grant everything on all databases. Top level admin user.

— Think of this like a DBA user in Oracle terms.

GRANT ALL PRIVILEGES ON *.* TO ‘adminuser’@’localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

— Grant everything on a specific database. Admin user for a specific database.
— Think of this like the schema-owner in Oracle terms.

GRANT ALL PRIVILEGES ON mydb.* TO ‘dbadminuser’@’localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

— Grant everything on a specific table.

GRANT ALL PRIVILEGES ON mydb.mytable TO ‘tableadminuser’@’localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

Typically, you would want to grant access on a more granular level. Remember, you should always attempt to give people the least privilege possible to
do their job.

— Allow read-only access to all tables in database.

GRANT SELECT ON mydb.* TO ‘rouser’@’%’;
FLUSH PRIVILEGES;

— Allow read-only access to specific tables.

GRANT SELECT ON mydb.tab1 TO ‘rouser’@’%’;
GRANT SELECT ON mydb.tab2 TO ‘rouser’@’%’;
FLUSH PRIVILEGES;

— Allow a variety of access to a variety of objects.

GRANT SELECT ON mydb.tab1 TO ‘myuser’@’%’;
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.tab2 TO ‘myuser’@’%’;
GRANT SELECT, UPDATE ON mydb.tab3 TO ‘myuser’@’%’;
GRANT SELECT, DELETE ON mydb.tab4 TO ‘myuser’@’%’;
GRANT EXECUTE ON mydb.proc1 TO ‘myuser’@’%’;
FLUSH PRIVILEGES;

——————————————————————————————————————————————————–

Removing privileges is essentially the opposite of what you’ve just seen.

— Remove all privleges on a specific database.
REVOKE ALL PRIVILEGES ON mydb.* FROM ‘adminuser’@’localhost’;

— Remove specific privileges from specific objects.
REVOKE EXECUTE ON FUNCTION `system_admin_db`.`user_count` FROM ‘myuser’@’%’;
GRANT SELECT ON mydb.tab2 TO ‘rouser’@’%’;

FLUSH PRIVILEGES;

Notice the FLUSH PRIVILEGES command, which reloads the privilege information from the relevant tables in the “mysql” schema.
Roles

——————————————————————————————————————————————————–

Display DDL for Users and Permissions

To display the DDL required to recreate the user, including the permissions, run the following command, substituting the correct user name.

SHOW GRANTS FOR ‘adminuser’@’%’;

——————————————————————————————————————————————————–

As mentioned in the previous section, MySQL does not support roles in the same sense as Oracle roles. Some tools, like MySQL Workbench, allow you to
model roles, but ultimately these are implemented using direct grants on users. There are some simple ways you can achieve similar results yourself.

Scripts : Maintain scripts containing all the relevant grants for a specific role. When that role is required by a user, simply run the script to
perform all the necessary grants.
Stored Procedures: Maintain stored procedures containing all the relevant grants for a specific role. When that role is required by a user, simply
run the stored procedure to perform all the necessary grants.

In both these cases, it makes sense to start any role change with the removal of all privileges, then application of the necessary roles.

REVOKE ALL PRIVILEGES ON *.* FROM ‘myuser’@’%’;
CALL `system_admin_db`.`hr_user_role`(‘myuser’, ‘%’);
CALL `system_admin_db`.`crm_user_role`(‘myuser’, ‘%’);

——————————————————————————————————————————————————–

On occasion you might need a user to perform a task that requires a very high degree of privilege, which you are not happy granting directly to them.
In this case, you can perform the action in a stored procedure or function and grant EXECUTE on that stored procedure or function to the user,
removing the need to grant the permission directly to the user.

CREATE DATABASE system_admin_db;
USE system_admin_db;

DROP FUNCTION IF EXISTS `system_admin_db`.`user_count`;

DELIMITER //
CREATE DEFINER = ‘root’@’localhost’ FUNCTION `system_admin_db`.`user_count` (
p_user VARCHAR(16)
)
RETURNS INT
READS SQL DATA
BEGIN
DECLARE l_count INT DEFAULT 0;

SELECT COUNT(*)
INTO l_count
FROM mysql.user
WHERE user = p_user;

RETURN l_count;
END //
DELIMITER ;

GRANT EXECUTE ON FUNCTION `system_admin_db`.`user_count` TO ‘myuser’@’%’;
FLUSH PRIVILEGES;

——————————————————————————————————————————————————–

MySQL-Connections in SQL Developer
———————————–

Downloads
Setup

Downloads

SQL Developer
JDBC Driver for MySQL (Connector/J) (platform independent version)

Setup

Download the latest “JDBC Driver for MySQL (Connector/J)” from here. Select the platform independent version and download the zip file.

Unzip the connector. The resulting directory contains a “mysql-connector-java-5.1.30-bin.jar” file.

Open SQL Developer and navigate to “Tools > Preferences > Database > Third Party JDBC Driver”.

Click the “Add Entry…” button and highlight the “mysql-connector-java-5.1.30-bin.jar” file and click the “Select” button.

Click the “OK” button to exit the “Preferences” dialog.

When you create a new connection, you will notice a “MySQL” tab has now appeared. Enter the connection details and test the connection.

MySQL Database Security Best Practices
—————————————
1. Secure your server

Many known attacks are possible only once physical access to a machine has been acquired. For this reason,
it is best to have the application server and the database server on different machines. If this is not possible, greater care must be taken;
otherwise, by executing remote commands via an application server, an attacker may be able to harm your database even without permissions.
For this reason, any service running on the same machine as the database should be granted the lowest possible permission that will still allow the
service to operate.

Do not forget to install the whole security package: Antivirus and Antispam, Firewall, and all of the security packages recommended by your operating
system’s vendor. In addition, do not forget to spend 10 minutes thinking of your server’s physical location – in the wrong location, your server can be stolen,
flooded, or harmed by wild animals or running children. Consider performing some operating system hardening procedures, such as the following:

Make sure to:

Install Antivirus and Antispam software
Configure the operating system’s firewall
Consider the safety of your server’s physical location
Install the services you intend the machine to run
Harden the production server and services
Disable unnecessary services
Follow services vendors’ recommendations regarding patches and updates needed for the safe and secure operation of their services

2. Disable or restrict remote access

Consider whether MySQL will be accessed from the network or only from its own server.

If remote access is used, ensure that only defined hosts can access the server. This is typically done through TCP wrappers, iptables, or any other
firewall software or hardware available on the market.

To restrict MySQL from opening a network socket, the following parameter should be added in the[mysqld] section of my.cnf or my.ini:

skip-networking

The file is located in the “C:\Program Files\MySQL\MySQL Server 5.1” directory on the Windows operating system or “/etc/my.cnf” or “/etc/mysql/my.cnf” on Linux.

This line disables the initiation of networking during MySQL startup. Please note that a local connection can still be established to the MySQL server.

Another possible solution is to force MySQL to listen only to the localhost by adding the following line in the [mysqld] section of my.cnf

bind-address=127.0.0.1

You may not be willing to disable network access to your database server if users in your organization connect to the server from their machines or the
web server installed on a different machine. In that case, the following restrictive grant syntax should be considered:

mysql> GRANT SELECT, INSERT ON mydb.* TO ‘someuser’@’somehost’;
3. Disable the use of LOCAL INFILE

The next change is to disable the use of the “LOAD DATA LOCAL INFILE” command, which will help to prevent unauthorized reading from local files. This is
especially important when new SQL Injection vulnerabilities in PHP applications are found.

In addition, in certain cases, the “LOCAL INFILE” command can be used to gain access to other files on the operating system, for instance “/etc/passwd”,
using the following command:

mysql> LOAD DATA LOCAL INFILE ‘/etc/passwd’ INTO TABLE table1

Or even simpler:

mysql> SELECT load_file(“/etc/passwd”)

To disable the usage of the “LOCAL INFILE” command, the following parameter should be added in the [mysqld] section of the MySQL configuration file.

set-variable=local-infile=0
4. Change root username and password

The default administrator username on the MySQL server is “root”. Hackers often attempt to gain access to its permissions. To make this task harder,
rename “root” to something else and provide it with a long, complex alphanumeric password.

To rename the administrator’s username, use the rename command in the MySQL console:

mysql> RENAME USER root TO new_user;

The MySQL “RENAME USER” command first appeared in MySQL version 5.0.2. If you use an older version of MySQL, you can use other commands to rename a user:

mysql> use mysql;
mysql> update user set user=”new_user” where user=”root”;
mysql> flush privileges;

To change a user’s password, use the following command-line command:

mysql> SET PASSWORD FOR ‘username’@’%hostname’ = PASSWORD(‘newpass’);

It is also possible to change the password using the “mysqladmin” utility:

shell> mysqladmin -u username -p password newpass
5. Remove the “test” database

MySQL comes with a “test” database intended as a test space. It can be accessed by the anonymous user, and is therefore used by numerous attacks.

To remove this database, use the drop command as follows:

mysql> drop database test;

Or use the “mysqladmin” command:

shell> mysqladmin -u username -p drop test
6. Remove Anonymous and obsolete accounts

The MySQL database comes with some anonymous users with blank passwords. As a result, anyone can connect to the database To check whether this is the case,
do the following:

mysql> select * from mysql.user where user=””;

In a secure system, no lines should be echoed back. Another way to do the same:

mysql> SHOW GRANTS FOR ”@’localhost’;
mysql> SHOW GRANTS FOR ”@’myhost’;

If the grants exist, then anybody can access the database and at least use the default database”test”. Check this with:

shell> mysql -u blablabla

To remove the account, execute the following command:

mysql> DROP USER “”;

The MySQL “DROP USER” command is supported starting with MySQL version 5.0. If you use an older version of MySQL, you can remove the account as follows:

mysql> use mysql;
mysql> DELETE FROM user WHERE user=””;
mysql> flush privileges;

7. Lower system privileges; increase database security with Role Based Access Control

A very common database security recommendation is to lower the permissions given to various parties. MySQL is no different. Typically, when developers work,
they use the system’s maximum permission and give less consideration to permission principles than we might expect. This practice can expose the
database to significant risk.

* Any new MySQL 5.x installation already installed using the correct security measures.

To protect your database, make sure that the file directory in which the MySQL database is actually stored is owned by the user “mysql” and the group “mysql”.

shell>ls -l /var/lib/mysql

In addition, ensure that only the user “mysql” and “root” have access to the directory/var/lib/mysql.

The mysql binaries, which reside under the /usr/bin/ directory, should be owned by “root” or the specific system “mysql” user. Other users should not
have write access to these files.

shell>ls -l /usr/bin/my*

8. Lower database privileges

Operating system permissions were fixed in the preceding section. Now let’s talk about database permissions. In most cases, there is an administrator
user (the renamed “root”) and one or more actual users who coexist in the database. Usually, the “root” has nothing to do with the data in the database;
instead, it is used to maintain the server and its tables, to give and revoke permissions, etc.

On the other hand, some user ids are used to access the data, such as the user id assigned to the web server to execute “select\update\insert\delete”
queries and to execute stored procedures. In most cases, no other users are necessary; however, only you, as a system administrator can really know
your application’s needs.

Only administrator accounts need to be granted the SUPER / PROCESS /FILE privileges and access to the mysql database. Usually, it is a good idea to

lower the administrator’s permissions for accessing the data.

Review the privileges of the rest of the users and ensure that these are set appropriately. This can be done using the following steps.

mysql> use mysql;

[Identify users]

mysql> select * from users;

[List grants of all users]

mysql> show grants for ‘root’@’localhost’;

The above statement has to be executed for each user ! Note that only users who really need root privileges should be granted them.

Another interesting privilege is “SHOW DATABASES”. By default, the command can be used by everyone having access to the MySQL prompt.
They can use it to gather information (e.g., getting database names) before attacking the database by, for instance, stealing the data.
To prevent this, it is recommended that you follow the procedures described below.

Add ” –skip-show-database” to the startup script of MySQL or add it to the MySQL configuration file
Grant the SHOW DATABASES privilege only to the users you want to use this command

To disable the usage of the “SHOW DATABASES” command, the following parameter should be added in the [mysqld] section of the /etc/my.cnf:

[mysqld]
skip-show-database

9. Enable Logging

If your database server does not execute many queries, it is recommended that you enable transaction logging, by adding the following line to [mysqld]
section of the /etc/my.cnf file:

[mysqld]
log =/var/log/mylogfile

This is not recommended for heavy production MySQL servers because it causes high overhead on the server.

In addition, verify that only the “root” and “mysql” ids have access to these logfiles (at least write access).

Error log

Ensure only “root” and “mysql” have access to the logfile “hostname.err”. The file is stored in the mysql data directory. This file contains very
sensitive information such as passwords, addresses, table names, stored procedure names and code parts. It can be used for information gathering,
and in some cases, can provide the attacker with the information needed to exploit the database, the machine on which the database is installed,
or the data inside it.

MySQL log

Ensure only “root” and “mysql” have access to the logfile “*logfileXY”. The file is stored in the mysql data directory.

10. Change the root directory

A chroot on Unix operating systems is an operation that changes the apparent disk root directory for the current running process and its children.
A program that is re-rooted to another directory cannot access or name files outside that directory, and the directory is called a “chroot jail” or
(less commonly) a “chroot prison”.

By using the chroot environment, the write access of the MYSQL processes (and child processes) can be limited, increasing the security of the server.

Ensure that a dedicated directory exists for the chrooted environment. This should be something like:/chroot/mysqlIn addition, to make the use of the
database administrative tools convenient, the following parameter should be changed in the [client] section of MySQL configuration file:

[client]
socket = /chroot/mysql/tmp/mysql.sock

Thanks to that line of code, there will be no need to supply the mysql, mysqladmin, mysqldump etc. commands with the –socket=/chroot/mysql/tmp/mysql.sock
parameter every time these tools are run.

11. Remove History

During the installation procedures, there is a lot of sensitive information that can assist an intruder to assault a database. This information is
stored in the server’s history and can be very helpful if something goes wrong during the installation. By analyzing the history files, administrators can
figure out what has gone wrong and probably fix things up. However, these files are not needed after installation is complete.

We should remove the content of the MySQL history file (~/.mysql_history), where all executed SQL commands are stored (especially passwords, which are
stored as plain text):

cat /dev/null > ~/.mysql_history

Selecting a database:

mysql> USE database;

Listing databases:

mysql> SHOW DATABASES;

Listing tables in a db:

mysql> SHOW TABLES;

Describing the format of a table:

mysql> DESCRIBE table;

Creating a database:

mysql> CREATE DATABASE db_name;

Creating a table:

……..SHOW TABLE STATUS FROM tutorial;
show status;

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Load tab-delimited data into a table:

mysql> LOAD DATA LOCAL INFILE “infile.txt” INTO TABLE table_name;
(Use \n for NULL)

Inserting one row at a time:

mysql> INSERT INTO table_name VALUES (‘MyName’, ‘MyOwner’, ‘2002-08-31’);
(Use NULL for NULL)

Retrieving information (general):

mysql> SELECT from_columns FROM table WHERE conditions;

All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = “value”;
Multiple critera: SELECT * FROM TABLE WHERE rec1 = “value1” AND rec2 = “value2”;

Reloading a new data set into existing table:

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE “infile.txt” INTO TABLE table;

Fixing all records with a certain value:

mysql> UPDATE table SET column_name = “new_value” WHERE record_name = “value”;

Selecting specific columns:

mysql> SELECT column_name FROM table;

Retrieving unique output records:

mysql> SELECT DISTINCT column_name FROM table;

Sorting:

mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculations:

mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching:

mysql> SELECT * FROM table WHERE rec LIKE “blah%”;
(% is wildcard – arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like “_____”;
(_ is any single character)

Extended Regular Expression Matching:

mysql> SELECT * FROM table WHERE rec RLIKE “^b$”;
(. for char, […] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use “REGEXP BINARY”

Counting Rows:

mysql> SELECT COUNT(*) FROM table;

Grouping with Counting:

mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each ‘owner’)

Selecting from multiple tables:

(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can join a table to itself to compare by using ‘AS’)

Currently selected database:

mysql> SELECT DATABASE();

Maximum value:

mysql> SELECT MAX(col_name) AS label FROM table;

Auto-incrementing rows:

mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES (“tom”),(“dick”),(“harry”);

Adding a column to an already-created table:

mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

Removing a column:

mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)

Batch mode (feeding in a script):

# mysql -u user -p < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> source batch_file;

Backing up a database with mysqldump:

# mysqldump –opt -u username -p database > database_backup.sql
(Use ‘mysqldump –opt –all-databases > all_backup.sql’ to backup everything.)
(More info at MySQL’s docs.)

Deleting all the rows in a table

mysql> TRUNCATE TABLE PET;

————————————————————————————————————————————————–

**MySQL server is running or not under Linux / UNIX operating systems?

You can use mysql startup script or mysqladmin command to find out if it is running on Linux.
Then you can use ps command and telnet command too (it is not reliable but it works.). mysqladmin is a utility for performing administrative operations.
You can also use shell script to monitor MySQL server. You can use mysqladmin as follows:

# mysqladmin -u root -p status
Output:

Enter password:
Uptime: 4 Threads: 1 Questions: 62 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 45 Queries per second avg: 15.500

Under Debian Linux you can type following command to find out if MySQL server is running or not

# /etc/init.d/mysql status Output:

If you are using RedHat of Fedora then you can use following script”

# service mysqld status

OR

# /etc/init.d/mysqld status

————————————————————————————————————————————————————-

** 1. How to change the MySQL root user password?

# mysqladmin -u root -ptmppassword password ‘newpassword’

# mysql -u root -pnewpassword

2. How to check whether MySQL Server is up and running?

# mysqladmin -u root -p ping
Enter password:
mysqld is alive

3. How do I find out what version of MySQL I am running?

# mysqladmin -u root -ptmppassword version

4. What is the current status of MySQL server?

# mysqladmin -u root -ptmppassword status

Uptime: 9267148
Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25

The status command displays the following information:

Uptime: Uptime of the mysql server in seconds
Threads: Total number of clients connected to the server.
Questions: Total number of queries the server has executed since the startup.
Slow queries: Total number of queries whose execution time waas more than long_query_time variable’s value.
Opens: Total number of tables opened by the server.
Flush tables: How many times the tables were flushed.
Open tables: Total number of open tables in the database.

5. How to view all the MySQL Server status variable and it’s current value?

# mysqladmin -u root -ptmppassword extended-status

6. How to display all MySQL server system variables and the values?

# mysqladmin -u root -ptmppassword variables

7. How to display all the running process/queries in the mysql database?

# mysqladmin -u root -ptmppassword processlist

You can use this command effectively to debug any performance issue and identify the query that is causing problems,
by running the command automatically every 1 second as shown below.

# mysqladmin -u root -ptmppassword -i 1 processlist

8. How to create a MySQL Database?

# mysqladmin -u root -ptmppassword create testdb

9. How to Delete/Drop an existing MySQL database?

# mysqladmin -u root -ptmppassword drop testdb

10. How to reload/refresh the privilege or the grants tables?

# mysqladmin -u root -ptmppassword reload;

Refresh command will flush all the tables and close/open log files.

# mysqladmin -u root -ptmppassword refresh

11. What is the safe method to shutdown the MySQL server?

# mysqladmin -u root -ptmppassword shutdown

# mysql -u root -ptmppassword
ERROR 2002 (HY000): Can’t connect to local MySQL server
through socket ‘/var/lib/mysql/mysql.sock’

Note: You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server.
To start the server, execute “/etc/rc.d/init.d/mysql start”

12. List of all mysqladmin flush commands.

# mysqladmin -u root -ptmppassword flush-hosts
# mysqladmin -u root -ptmppassword flush-logs
# mysqladmin -u root -ptmppassword flush-privileges
# mysqladmin -u root -ptmppassword flush-status
# mysqladmin -u root -ptmppassword flush-tables
# mysqladmin -u root -ptmppassword flush-threads

flush-hosts: Flush all information in the host cache.
flush-privileges: Reload the grant tables (same as reload).
flush-status: Clear status variables.
flush-threads: Flush the thread cache.

13. How to kill a hanging MySQL Client Process?

First identify the hanging MySQL client process using the processlist command.

# mysqladmin -u root -ptmppassword processlist
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 20 | root | localhost | | Sleep | 64 | | |
| 24 | root | localhost | | Query | 0 | | show processlist |
+—-+——+———–+—-+———+——+——-+——————+

Now, use the kill command and pass the process_id as shown below. To kill multiple process you can pass comma separated process id’s.

# mysqladmin -u root -ptmppassword kill 20

# mysqladmin -u root -ptmppassword processlist
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 26 | root | localhost | | Query | 0 | | show processlist |
+—-+——+———–+—-+———+——+——-+——————+

14. How to start and stop MySQL replication on a slave server?

# mysqladmin -u root -ptmppassword stop-slave
Slave stopped

# mysqladmin -u root -ptmppassword start-slave
mysqladmin: Error starting slave: The server is not configured as slave;
fix in config file or with CHANGE MASTER TO

15. How to combine multiple mysqladmin commands together?

# mysqladmin -u root -ptmppassword process status version

# mysqladmin -u root -ptmppassword pro stat ver

Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.

# mysqladmin -h 192.168.1.112 -u root -ptmppassword pro stat ver

————————————————————————————————————————————————————–

Running and Shutting down MySQL Server:

First check if your MySQL server is running or not. You can use the following command to check this:

ps -ef | grep mysqld

If your MySql is running, then you will see mysqld process listed out in your result.
If server is not running, then you can start it by using the following command:

root@host# cd /usr/bin
./safe_mysqld &

Now, if you want to shut down an already running MySQL server, then you can do it by using the following command:

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

Setting Up a MySQL User Account:

For adding a new user to MySQL, you just need to add a new entry to user table in database mysql.

Below is an example of adding new user guest with SELECT, INSERT and UPDATE privileges with the password guest123;
the SQL query is:

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES (‘localhost’, ‘guest’,
PASSWORD(‘guest123’), ‘Y’, ‘Y’, ‘Y’);

mysql> FLUSH PRIVILEGES;

mysql> SELECT host, user, password FROM user WHERE user = ‘guest’;

When adding a new user, remember to encrypt the new password using PASSWORD() function provided by MySQL.
As you can see in the above example the password mypass is encrypted to 6f8c114b58f2ce9e.

Notice the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables.
If you don’t use it, then you won’t be able to connect to mysql using the new user account at least until the server is rebooted.

You can also specify other privileges to a new user by setting the values of following columns in user table to ‘Y’
when executing the INSERT query or you can update them later using UPDATE query.

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv

Another way of adding user account is by using GRANT SQL command; following example will add user zara with
password zara123 for a particular database called TUTORIALS.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO ‘zara’@’localhost’
-> IDENTIFIED BY ‘zara123’;

This will also create an entry in mysql database table called user.

—————————————————————————————————————————

NOTE: MySQL does not terminate a command until you give a semi colon (;) at the end of SQL command.

The /etc/my.cnf File Configuration:

Most of the cases, you should not touch this file. By default, it will have the following entries:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Here, you can specify a different directory for error log, otherwise you should not change any entry in this table.

Here is the list of important MySQL commands, which you will use time to time to work with MySQL database:

USE Databasename : This will be used to select a particular database in MySQL workarea.
SHOW DATABASES: Lists the databases that are accessible by the MySQL DBMS.
SHOW TABLES: Shows the tables in the database once a database has been selected with the use command.
SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults,
and other information for a table.
SHOW INDEX FROM tablename: Presents the details of all indexes on the table, including the PRIMARY KEY.
SHOW TABLE STATUS LIKE tablenameG: Reports details of the MySQL DBMS performance and statistics.

—————————————————————————————————————————————————————–

Capturing Output from MySQL
—————————
If you want to divert the output of a SQL query to a log file, use the \T command along with a filename.

For instance, to write the output of a query to query.txt, do the following:

mysql> \T output.txt
Logging to file ‘query.txt’

———————————————————–

Exiting the mysql Program

To exit the mysql program, use the \q command.

Alternatively, you can type quit or exit to leave the program

—————————————————————

If you want to run a single host command without exiting mysql, use the \! command.

The following example runs the system command pwd on a UNIX/Linux system to determine the path of the current working directory:

mysql> \! pwd
/home/chris/public_html
——————————————————————

–SHOW create database world;

–Find MySQL database size

If you need to find out the size of MySQL databases you use from terminal, the following query will list all the databases with their respective sizes:

mysql> SELECT table_schema “database”, sum(data_length + index_length)/1024/1024 “size in MB” FROM information_schema.TABLES GROUP BY table_schema;

The result you’ll get will be something like:

| database | size in MB |
+——————–+—————-+
| test1 | 13542.68241349 |
| test2 | 1522.23837675 |
| test3 | 26532.27326164 |
| information_schema | 0.00390626 |
+——————–+—————-+
4 rows in set (0.02 sec)

If you have large databases, you can show the result in gigabytes with this query:

mysql> SELECT table_schema “database”, sum(data_length + index_length)/1024/1024/1024 “size in GB” FROM information_schema.TABLES GROUP BY table_schema;

—2nd Method–Locate the MySQL stored data—

Access this article to find out where does MySQL database saved the data.

Windows
Locate the MySQL ibdata1 file, right click on the file and click the properties, see the size? 🙂

Linux
Locate the MySQL ibdata1 file

mkyong@myserver:/var/lib/mysql$ ls -lh
total 1.5G

—————————————————————————————————————————-

CREATE TABLE IF NOT EXISTS newauthor

CREATE TABLE author_copy LIKE author;

CREATE TABLE author_copy AS SELECT * FROM author;

SET FOREIGN_KEY_CHECKS=0;

SET FOREIGN_KEY_CHECKS=1;

SELECT @@FOREIGN_KEY_CHECKS

# To turn off foreign key constraint globally, do the following:

SET GLOBAL FOREIGN_KEY_CHECKS=0;

and remember to set it back when you are done

SET GLOBAL FOREIGN_KEY_CHECKS=1;

SELECT @@FOREIGN_KEY_CHECKS

WARNING: You should only do this when you are doing single user mode maintenance. As it might resulted in data inconsistency.
For example, it will be very helpful when you are uploading large amount of data using a mysqldump output.

SHOW TABLE STATUS FROM test;

SHOW CREATE TABLE foobar;

SHOW CHARACTER SET;

SHOW COLLATION LIKE ‘latin1%’;

SET NAMES ‘utf8′;

SHOW PLUGINS;

INSERT INTO testpurchase SELECT * FROM purchase;

INSERT INTO testpurchase SELECT * FROM purchase WHERE YEAR(invoice_dt)=’2008’;