The MySQL Database Installation and configuration:

—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

Advertisements

MySQL : Users and Permissions Management

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

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

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

MySQL Commands

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’;

MySQL All in one ACTIVITY

CentOS Linux 5/6 Install Mysql Database Server
———————————————-
How do I install MySQL database server on CentOS Linux version 5 or version 6 using command line options? How do I configuring MySQL server on CentOS Linux?

You need to install the following packages on CentOS Linux:
Tutorial details
Difficulty Easy (rss)
Root privileges Yes
Requirements yum/Centos/ssh
Estimated completion time 15m

mysql-server : The MySQL server and related files
mysql : MySQL client programs and shared libraries

Additionally, you may need to install the following packages to access mysql using various programming languages:

php-mysql : A module for PHP applications that use MySQL databases.
perl-DBD-MySQL : A MySQL interface for perl.
MySQL-python : An interface to MySQL.

How to install MySQL on CentOS

To install mysql server type the following yum command:
# yum install mysql-server mysql
Sample outputs:
Loaded plugins: downloadonly, fastestmirror, security
Loading mirror speeds from cached hostfile
* base: mirrors.einstein.yu.edu
* extras: mirrors.loosefoot.com
* updates: yum.singlehop.com
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package mysql.x86_64 0:5.1.71-1.el6 will be installed
—> Package mysql-server.x86_64 0:5.1.71-1.el6 will be installed
–> Processing Dependency: perl-DBI for package: mysql-server-5.1.71-1.el6.x86_64
–> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.1.71-1.el6.x86_64
–> Processing Dependency: perl(DBI) for package: mysql-server-5.1.71-1.el6.x86_64
–> Running transaction check
—> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
—> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

===================================================================
Package Arch Version Repository
Size
===================================================================
Installing:
mysql x86_64 5.1.71-1.el6 base 893 k
mysql-server x86_64 5.1.71-1.el6 base 8.6 M
Installing for dependencies:
perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k
perl-DBI x86_64 1.609-4.el6 base 705 k

Transaction Summary
===================================================================
Install 4 Package(s)

Total download size: 10 M
Installed size: 29 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): mysql-5.1.71-1.el6.x86_64.rpm | 893 kB 00:00
(2/4): mysql-server-5.1.71-1.el6.x86_64.rpm | 8.6 MB 00:01
(3/4): perl-DBD-MySQL-4.013-3.el6.x86_64.rp | 134 kB 00:00
(4/4): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:00
——————————————————————-
Total 4.2 MB/s | 10 MB 00:02
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : perl-DBI-1.609-4.el6.x86_64 1/4
Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 2/4
Installing : mysql-5.1.71-1.el6.x86_64 3/4
Installing : mysql-server-5.1.71-1.el6.x86_64 4/4
Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 1/4
Verifying : mysql-server-5.1.71-1.el6.x86_64 2/4
Verifying : mysql-5.1.71-1.el6.x86_64 3/4
Verifying : perl-DBI-1.609-4.el6.x86_64 4/4

Installed:
mysql.x86_64 0:5.1.71-1.el6 mysql-server.x86_64 0:5.1.71-1.el6

Dependency Installed:
perl-DBD-MySQL.x86_64 0:4.013-3.el6
perl-DBI.x86_64 0:1.609-4.el6

Complete!

Import CentOS Mysql server files and ports

Mysql server config file: /etc/my.cnf
Mysql default tcp port: 3306 (TCP/UDP)
Mysql server log file: /var/log/mysqld.log
Mysql database storage directory: /var/lib/mysql/
Mysql rc script to start/stop/restart server: /etc/init.d/mysqld {start|stop|restart|reload|status|try-restart}

Howto configure MySQL on CentOS Linux

First, turn on mysql service, type the following chkconfig command:
# chkconfig mysqld on
Howto start / stop / restart mysql server on CentOS Linux

Use any one of the following command to start / stop / restart the mysql server:
# service mysqld start
# service mysqld restart
# service mysqld stop
OR
# /etc/init.d/mysqld start
# /etc/init.d/mysqld stop
# /etc/init.d/mysqld restart
Sample outputs:exit6
Fig.01: Starting / stopping / restarting the mysql server on centos

Fig.01: Starting / stopping / restarting the mysql server on centos

Set the root user password

The root user is mysql admin user. By default there is no password assigned to the root user. Use the following command to set a
new password for the root user:

# mysqladmin -u root password NEWPASSWORD
See how to Change/update the root password for mysqld for more information.
Configure mysqld via /etc/my.cnf config file

Edit /etc/my.cnf, enter:
# vi /etc/my.cnf
Sample default config file:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Mysql CentOS Linux firewall configuration

Edit /etc/sysconfig/iptables file, enter:
# vi /etc/sysconfig/iptables
Append the following code open tcp port 3306 on CentOS base server:

-A INPUT -m state –state NEW,ESTABLISHED -m tcp -p tcp –dport 3306 -j ACCEPT

Save and close the file. To restart the firewall on CentOS/RHEL, enter:
# service iptables restart
See CentOS / Redhat Iptables Firewall Configuration Tutorial for more information.
How do I view mysql server logs?

To see server error log use the following tail command:
# tail -f /var/log/mysqld.log
Sample outputs:

131202 11:44:41 [Note] /usr/libexec/mysqld: Shutdown complete
131202 11:44:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
131202 11:44:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131202 11:44:41 InnoDB: Initializing buffer pool, size = 8.0M
131202 11:44:41 InnoDB: Completed initialization of buffer pool
131202 11:44:41 InnoDB: Started; log sequence number 0 44233
131202 11:44:41 [Note] Event Scheduler: Loaded 0 events
131202 11:44:41 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.1.71’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Source distribution

How do I connect to mysql server?

Use the mysql command line as follows:
$ mysql -u USER-NAME-HERE -p’PASSWORD-HERE’
$ mysql -u USER-NAME-HERE -p’PASSWORD-HERE’ -h ‘SERVER-NAME-HOST-NAME-HERE’
$ mysql -u USER-NAME-HERE -p’PASSWORD-HERE’ -h ‘SERVER-NAME-HOST-NAME-HERE’ DB-NAME_HERE
$ mysql -u root -p
Sample outputs:

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

————————————————————————————————–
How To Start and Stop MySQL Database Server From A Shell Prompt
————————————————————————————————–

Q. How do I start and stop mysql server under Debian / Ubuntu Linux system?

A. Simply use service or /etc/init.d/mysql script to start / stop / restart mysql database server.
Task: Start mysql server

# service mysql start
# /etc/init.d/mysql start

OR
$ sudo service mysql start
$ sudo /etc/init.d/mysql start
Task: Stop mysql server

# service mysql stop
# /etc/init.d/mysql stop

OR
$ sudo service mysql stop
$ sudo /etc/init.d/mysql stop
To restart simply, use:
$ sudo service mysql restart
$ sudo /etc/init.d/mysql restart

————————————————————————————————–
MySQL Change root Password
————————————————————————————————–
How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX-like like operating system over the ssh session?

Setting up MySQL password is one of the essential tasks. By default, root user is MySQL admin account user.
Please note that the Linux or UNIX root account for your operating system and MySQL root user accounts are different.
They are separate, and nothing to do with each other. Sometime you may remove Mysql root account and setup admin user as super user for security purpose.
Method #1: 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’

Note:123456 password is used for demonstration purpose only. You must select a strong password.
It is an important protection to help you have safer MySQL database transactions.
Sample live session from my home server using mysqladmin

Fig.01: mysqladmin command in action

Fig.01: mysqladmin command in action
How do I verify that the new password is working or not?

Use the following mysql command:

mysql -u root -p’123456′ db-name-here

OR

mysql -u root -p’123456′ -e ‘show databases;’
A note about changing MySQL password for other users

To change a normal user password you need to type the following command. In this example, change the password for nixcraft mysql user:
$ mysqladmin -u nixcraft -p’old-password’ password new-password
Method #2: Changing MySQL root user password using the mysql command

This is an another method. MySQL stores username and passwords in user table inside MySQL database.
You can directly update or change the password using the following method for user called nixcraft:
Login to mysql server, type the following command at shell prompt:

$ mysql -u root -p
Use mysql database (type command at mysql> prompt):

mysql> use mysql;

Change password for user nixcraft, enter:

mysql> update user set password=PASSWORD(“NEWPASSWORD”) where User=’nixcraft’;

Finally, reload the privileges:

mysql> flush privileges;
mysql> quit
—————————————————————————————————————————————————-

(OR) Howto Install MySQL on Linux
—————————————————————————————————————————————————-

Most of the Linux distro comes with MySQL. If you want use MySQL, my recommendation is that you download the latest version of MySQL and install it yourself.
Later you can upgrade it to the latest version when it becomes available. In this article,
I will explain how to install the latest free community edition of MySQL on Linux platform.
1. Download the latest stable relase of MySQL

Download mySQL from mysql.com . Please download the community edition of MySQL for your appropriate Linux platform.
I downloaded the “Red Hat Enterprise Linux 5 RPM (x86)”. Make sure to download MySQL Server, Client and “Headers and libraries” from the download page.

MySQL-client-community-5.1.25-0.rhel5.i386.rpm
MySQL-server-community-5.1.25-0.rhel5.i386.rpm
MySQL-devel-community-5.1.25-0.rhel5.i386.rpm

2. Remove the existing default MySQL that came with the Linux distro

Do not perform this on an system where the MySQL database is getting used by some application.

[local-host]# rpm -qa | grep -i mysql
mysql-5.0.22-2.1.0.1
mysqlclient10-3.23.58-4.RHEL4.1

[local-host]# rpm -e mysql –nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
[local-host]# rpm -e mysqlclient10

3. Install the downloaded MySQL package

Install the MySQL Server and Client packages as shown below.

[local-host]# rpm -ivh MySQL-server-community-5.1.25-0.rhel5.i386.rpm MySQL-client-community-5.1.25-0.rhel5.i386.rpm

Preparing… ########################################### [100%]
1:MySQL-client-community ########################################### [ 50%]
2:MySQL-server-community ########################################### [100%]

This will also display the following output and start the MySQL daemon automatically.

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h medica2 password ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

Starting MySQL.[ OK ]
Giving mysqld 2 seconds to start

Install the “Header and Libraries” that are part of the MySQL-devel packages.

[local-host]# rpm -ivh MySQL-devel-community-5.1.25-0.rhel5.i386.rpm
Preparing… ########################################### [100%]
1:MySQL-devel-community ########################################### [100%]

Note: When I was compiling PHP with MySQL option from source on the Linux system, it failed with the following error.
Installing the MySQL-devel-community package fixed this problem in installing PHP from source.

configure: error: Cannot find MySQL header files under yes.
Note that the MySQL client library is not bundled anymore!

4. Perform post-install security activities on MySQL.

At a bare minimum you should set a password for the root user as shown below:

[local-user]# /usr/bin/mysqladmin -u root password ‘My2Secure$Password’

The best option is to run the mysql_secure_installation script that will take care of all the typical security related items on the MySQL as shown below.
On a high level this does the following items:

Change the root password
Remove the anonymous user
Disallow root login from remote machines
Remove the default sample test database

[local-host]# /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we’ll need the current
password for the root user. If you’ve just installed MySQL, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
You already have a root password set, so you can safely answer ‘n’.
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
… Success!
Normally, root should only be allowed to connect from ‘localhost’. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
… Success!
By default, MySQL comes with a database named ‘test’ that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
… Success!
Cleaning up…
All done! If you’ve completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

5. Verify the MySQL installation:

You can check the MySQL installed version by performing mysql -V as shown below:

[local-host]# mysql -V
mysql Ver 14.14 Distrib 5.1.25-rc, for redhat-linux-gnu (i686) using readline 5.1

Connect to the MySQL database using the root user and make sure the connection is successfull.

[local-host]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

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

mysql>

Follows the steps below to stop and start MySQL

[local-host]# service mysql status
MySQL running (12588) [ OK ]
[local-host]# service mysql stop
Shutting down MySQL. [ OK ]
[local-host]# service mysql start
Starting MySQL. [ OK ]

———————————————————————————————————————————————
HowTo: Uninstall MySQL Server in Ubuntu Linux
———————————————————————————————————————————————

I’m a new Ubuntu Linux user and my cloud hosting company installed MySQL server by default.
I need to remove it and delete it from my server as I have no use of MySQL server. How can I uninstall MySQL on a Ubuntu based systems?

Typically following Mysql packages are installed on the Debian or Ubuntu Linux systems:

mysql-client – The latest version of MySQL database client.
mysql-server – The latest version of MySQL database server.
mysql-common – MySQL database common files.

How do I uninstall Mysql server?

Just use the apt-get command as follows remove both MySQL server and client in Ubuntu Linux:
sudo apt-get –purge remove mysql-client mysql-server mysql-common
sudo apt-get autoremove

Sample outputs (pay attention to package names):

Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages were automatically installed and are no longer required:
linux-headers-3.2.0-31-virtual linux-headers-3.2.0-31
Use ‘apt-get autoremove’ to remove them.
The following packages will be REMOVED:
libdbd-mysql-perl* libmysqlclient18* mysql-client* mysql-client-5.5* mysql-common* mysql-server*
mysql-server-5.5*
0 upgraded, 0 newly installed, 7 to remove and 0 not upgraded.
After this operation, 67.5 MB disk space will be freed.
Do you want to continue [Y/n]? y
(Reading database … 105097 files and directories currently installed.)
Removing mysql-server …
Removing mysql-server-5.5 …
mysql stop/waiting
Purging configuration files for mysql-server-5.5 …
Removing mysql-client …
Removing mysql-client-5.5 …
Removing libdbd-mysql-perl …
Removing libmysqlclient18 …
Purging configuration files for libmysqlclient18 …
Removing mysql-common …
Purging configuration files for mysql-common …
dpkg: warning: while removing mysql-common, directory ‘/etc/mysql’ not empty so not removed.
Processing triggers for ureadahead …
Processing triggers for man-db …
Processing triggers for libc-bin …
ldconfig deferred processing now taking place

Delete /etc/mysql/ directory using rm command:
$ sudo rm -rf /etc/mysql/
Understanding apt-get command options

–purge : Remove given packages and config files.
remove : Uninstall packages.
autoremove : Force to remove packages that were automatically installed to satisfy dependencies for other packages and are now no longer needed.

Live Monitoring of MySQL

There are two useful tools:

mytop
innotop

with “mytop” being an own Debian package, while “innotop” is included in the “mysql-client” package. From both innotop has the more advanced functionality. Both need to be called with credentials to connect to the database:

mytop -u <user> -p<password>
innotop -u <user> -p<password>

Alternatively you can provide a .mytop file to provide the credentials automatically.
Show MySQL Status
You can get a very simple status by just entering “\s” in the “mysql” command line client prompt:

mysql> \s

You can show the replication status using

SHOW SLAVE STATUS \G
SHOW MASTER STATUS \G

Note that the “\G” instead of “;” just makes the output more readable. If you have configured slaves to report names you can list them on the master with:

SHOW SLAVE HOSTS;

Check InnoDB status

show /*!50000 ENGINE*/ INNODB STATUS;

List Databases/Tables/Colums
You can either use the “mysqlshow” tool:

mysqlshow # List all databases
mysqlshow <database> # List all tables of the given database
mysqlshow <database> <table> # List all columns of the given table in the given DB

And you can also do it using queries:

SHOW DATABASES;

USE <database>;
SHOW TABLES;
DESCRIBE <table>;

Check and Change Live Configuration Parameters
Note that you cannot change all existing parameters. Some like innodb_pool_buffer require a DB restart.

show variables; # List all configuration settings
show variables like ‘key_buffer_size’; # List a specific parameter

set global key_buffer_size=100000000; # Set a specific parameter

# Finally ensure to edit my.cnf to make the change persistent

MySQL Parameter Optimization
You can check MySQL parameters of a running instance using tools like

MySQLTuner
MySQL Tuning – Primer
pt-variable-advisor

Also have a look at this MySQL config parameter explanation.
Remote MySQL Dump and Import
The following command allows dumping a database from one source host that doesn’t see the target host when executed on a third host that can access both. If both hosts can see each other and one has SSH access to the other you can simply drop one of the ssh calls.

ssh <user@source host> “mysqldump –single-transaction -u root –password=<DB root pwd> <DB name>” | ssh <user@target host> “mysql -u root –password=<DB root pwd> <DB name>”

Troubleshooting
How to solve: Could not find target log during relay log initialization
Happens on corrupted/missing relay logs. To get the DB working

Stop MySQL
Remove /var/lib/mysql/relay-log-index.*
Remove all relay log files
Remove relog log file index
Start MySQL

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
This is caused by timeouts when copying overly large database tables. The default network timeouts are very short per-default. So you can workaround this by increasing network timeouts

set global net_write_timeout = 100000;
set global net_read_timeout = 100000;

Dump Skip Event Table
If your MySQL backup tool or self-written script complains about an event table than you have run into an issue caused by newer MySQL versions (>5.5.30) that introduced a new table “events” in the internal schema. If you run into this you need to decide wether you want to include or exclude the new events table when dumping your database. To skip: Due to a MySQL bug #68376 you have two choices. You can check documentation and add the logical option

–skip-events

which will cause the event table not to be exported. But the warning won’t go away. To also get rid of the warning you need to use this instead:

–events –ignore-table=mysql.events

And of course you can also choose just to dump the events table: Add the option

–events

to your “mysqldump” invocation. If you use a tool that invokes “mysqldump” indirectly check if the tool allows to inject additional parameters.
Forgotten root Password

# 1. Stop MySQL and start without grant checks

/usr/bin/mysqld_safe –skip-grant-tables &
mysql –user=root mysql

# 2. Change root password
UPDATE user SET password=PASSWORD(‘xxxxx’) WHERE user = ‘root’;

Import a CSV file into MySQL

LOAD DATA IN ‘<CSV filename>’ INTO TABLE <table name> FIELDS TERMINATED BY ‘,’ (<name of column #1>,<<name of column #2>,<…>);

MySQL Pager – Output Handling
Using “PAGER” or \P you can control output handling. Instead of having 10k lines scrolling by you can write everything to a file or use “less” to scroll through it for example. To use less issue

pager less

Page output into a script

pager /home/joe/myscript.sh

Or if you have Percona installed get a tree-like “EXPLAIN” output with

pager mk-visual-explain

and then run the “EXPLAIN” query.
MySQL – Check Query Cache

# Check if enabled
SHOW VARIABLES LIKE ‘have_query_cache’;

# Statistics
SHOW STATUS LIKE ‘Qcache%’;

Check for currently running MySQL queries

show processlist;
show full processlist;

Filter items in process list by setting grep as a pager. The following example will only print replication connections:

\P grep system
show processlist;

To abort/terminate a statement determine it’s id and kill it:

kill <id>; # Kill running queries by id from process listing

Show Recent Commands

SHOW BINLOG EVENTS;
SHOW BINLOG EVENTS IN ‘<some bin file name>’;

Inspect a MySQL binlog file
There is an extra tool to inspect bin logs:

mysqlbinlog <binary log file>

Skip one statement on replication issue HA_ERR_FOUND_DUPP_KEY
If replication stops with “HA_ERR_FOUND_DUPP_KEY” you can skip the current statement and continue with the next one by running:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Changing Replication Format
When you want to change the replication format of a running setup you might want to follow this steps:

Ensure you have a database backup
Make master read-only by running

FLUSH TABLES WITH READ LOCK;

Wait until all slaves do catch up
Stop all slaves (shutdown MySQL)
On master:

FLUSH LOGS;
SET GLOBAL binlog_format=’xxxxxx’;
FLUSH LOGS;
UNLOCK TABLES;

(ensure to replace ‘xxxxxx’ with for example ‘ROW’)
Start all slaves
Ensure to put the new binlog_format in all /etc/mysql/my.cnf

Note: the second “FLUSH LOGS;” ensures that the a new binary log is opened on the master with the new binlog_format. The stopping of the slaves ensures that they open a new relay log matching the new binlog_format.
Munin MySQL Plugin Setup on Debian

apt-get install libcache-cache-perl

for i in `./mysql_ suggest`
do
do ln -sf /usr/share/munin/plugins/mysql_ $i;
done

/etc/init.d/munin-node reload

Fix Slow Replication
When replication is slow check the status of the replication connection. If it is too often in “invalidating query cache” status you need to decrease your query cache size. You might even consider disabling query cache for the moment if the DB load does allow it:

set global query_cache_size=0;

Debug DB Response Time
There is generic TCP response analysis tool developed by Percona called tcprstat. Download the binary from Percona, make it executable and run it like

tcprstat -p 3306 -t 1 -n 0

to get continuous statistics on the response time. This is helpful each time some developer claims the DB doesn’t respond fast enough!

MySQL Users and Permissions

–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’;
FLUSH PRIVILEGES;

use mysql;
CREATE USER ‘testuser’@’%’ IDENTIFIED BY ‘testuser’;
GRANT SELECT ON *.* TO ‘testuser’@’%’;
FLUSH PRIVILEGES;

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

mysql> select user,host from mysql.user;

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

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

========================================================================================================

mysql> SELECT host, user FROM mysql.user WHERE user = ‘adminuser’;
mysql> SELECT host, user FROM mysql.user;

==============================================================================================================================
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–

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.
use mysql;
SET PASSWORD = PASSWORD(‘newrootpassword’);
FLUSH PRIVILEGES;

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

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

–Renames a User–

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

–Drop a User–
use mysql;
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’;

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

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

Manage Privileges
——————-

— Grant everything on all databases– 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;

— 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

SHOW GRANTS FOR ‘adminuser’@’%’;

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

How to Remove MySQL Completely from Linux System

Note: Please do not use below steps if MySQL have any running databases.(Stop the server First)

Step 1: Uninstall MySQL Packages

# yum remove mysql mysql-server

(OR)

# rpm -e <rpm name>

Step 2: Remove MySQL Directory(Rename it to keep Backup also)

# mv /var/lib/mysql /var/lib/mysql_old_backup

Step 3: Install MySQL Packages Again

After removing MySQL completely, install it again using yum package manager, It will re create mysql directory under /var/lib/.

# yum install mysql mysql-server (OR) rpm -e <package name>

—-After completing above three steps, now you have a fresh MySQL install on your system with new settings—

Start,Restart or Stop MySQL Server

ps -ef|grep mysql —-(to get or check mysql server status or socket path)

mysql -u root -p –socket=’/var/mysql/mysql.sock’

Method-1(Recommended) To restart, start or stop MySQL server from the command line
———————————————————————————–
/etc/init.d/mysqld status

/etc/init.d/mysqld start
/etc/init.d/mysqld stop
/etc/init.d/mysqld restart
Method-2 Some Linux flavours offer the service command to
———————————————————
chkconfig mysqld on

service mysqld start

service mysqld stop

service mysqld restart
(OR)
——————–

service mysql start

service mysql stop

service mysql restart
(OR) Method-4 and (Last Method if above doesnt work) Running and Shutting down MySQL Server:
————————————————————————————–

First check if your MySQL server is running or not.

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: ******