UNIX Commands for DBAs

UNIX Commands for DBAs
———————–

This article contains a brief list of commands that most UNIX DBAs will need on a regular basis. Over time I’ve been adding more Linux-related entries.

–Basic File Navigation

The “pwd” command displays the current directory.

root> pwd
/u01/app/oracle/product/9.2.0.1.0

–The “ls” command lists all files and directories in the specified directory. If no location is defined it acts on the current directory.

root> ls
root> ls /u01
root> ls -al

The “-a” flag lists hidden “.” files. The “-l” flag lists file details.

–The “cd” command is used to change directories.

root> cd /u01/app/oracle

–The “touch” command is used to create a new empty file with the default permissions.

root> touch my.log

–The “rm” command is used to delete files and directories.

root> rm my.log
root> rm -R /archive

–The “-R” flag tells the command to recurse through subdirectories.

–The “mv” command is used to move or rename files and directories.

root> mv [from] [to]
root> mv my.log my1.log
root> mv * /archive
root> mv /archive/* .

–The “.” represents the current directory.

–The “cp” command is used to copy files and directories.

root> cp [from] [to]
root> cp my.log my1.log
root> cp * /archive
root> cp /archive/* .

–The “mkdir” command is used to create new directories.

root> mkdir archive

–The “rmdir” command is used to delete directories.

root> rmdir archive

–The “find” command can be used to find the location of specific files.

root> find / -name dbmspool.sql
root> find / -print | grep -i dbmspool.sql

–The “/” flag represents the starting directory for the search. Wildcards such as “dbms*” can be used for the filename.

–The “which” command can be used to find the location of an executable you are using.

oracle> which sqlplus

The “which” command searches your PATH setting for occurrences of the specified executable.
File Permissions

See Linux Files, Directories and Permissions.

–The “umask” command can be used to read or set default file permissions for the current user.

root> umask 022

The umask value is subtracted from the default permissions (666) to give the final permission.

666 : Default permission
022 : – umask value
644 : final permission

–The “chmod” command is used to alter file permissions after the file has been created.

root> chmod 777 *.log

Owner Group World Permission
========= ========= ========= ======================
7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute
6 (u+rw) 6 (g+rw) 6 (o+rw) read + write
5 (u+rx) 5 (g+rx) 5 (o+rx) read + execute
4 (u+r) 4 (g+r) 4 (o+r) read only
2 (u+w) 2 (g+w) 2 (o+w) write only
1 (u+x) 1 (g+x) 1 (o+x) execute only

Character eqivalents can be used in the chmod command.

root> chmod o+rwx *.log
root> chmod g+r *.log
root> chmod -Rx *.log

–The “chown” command is used to reset the ownership of files after creation.

root> chown -R oinstall.dba *

The “-R” flag causes the command ro recurse through any subdirectories.
OS Users Management

See Linux Groups and Users.

–The “useradd” command is used to add OS users.

root> useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user

The “-G” flag specifies the primary group.
The “-g” flag specifies the secondary group.
The “-d” flag specifies the default directory.
The “-m” flag creates the default directory.
The “-s” flag specifies the default shell.

–The “usermod” command is used to modify the user settings after a user has been created.

root> usermod -s /bin/csh my_user

–The “userdel” command is used to delete existing users.

root> userdel -r my_user

The “-r” flag removes the default directory.

–The “passwd” command is used to set, or reset, the users login password.

root> passwd my_user

–The “who” command can be used to list all users who have OS connections.

root> who
root> who | head -5
root> who | tail -5
root> who | grep -i ora
root> who | wc -l

The “head -5” command restricts the output to the first 5 lines of the who command.
The “tail -5” command restricts the output to the last 5 lines of the who command.
The “grep -i ora” command restricts the output to lines containing “ora”.
The “wc -l” command returns the number of lines from “who”, and hence the number of connected users.

Process Management
——————

See Linux Process Management (ps, top, renice, kill).

–The “ps” command lists current process information.

# ps
# ps -ef | grep -i ora
# ps -ef | grep -i ora | grep -v grep
# ps -ef | grep -i [o]ra

–Specific processes can be killed by specifying the process id in the kill command.

# kill 12345
# kill -9 12345

–You can kill multiple processes using a single command by combining “kill” with the “ps” and “awk” commands.

# kill -9 `ps -ef | grep ora | awk ‘{print $2}’`

uname and hostname

–The “uname” and “hostname” commands can be used to get information about the host.

root> uname -a
OSF1 oradb01.lynx.co.uk V5.1 2650 alpha

root> uname -a | awk ‘{ print $2 }’
oradb01.lynx.co.uk

root> hostname
oradb01.lynx.co.uk

–Compress Files

See Linux Archive Tools (tar, star, gzip, bzip2, zip, cpio).

In order to save space on the filesystem you may wish to compress files such as archived redo logs. This can be using either the gzip or the compress commands. The gzip command results in a compressed copy of the original file with a “.gz” extension. The gunzip command reverses this process.

gzip myfile
gunzip myfile.gz

–The compress command results in a compressed copy of the original file with a “.Z” extension. The uncompress command reverses this process.

compress myfile
uncompress myfile

–General Performance
vmstat

Reports virtual memory statistics.

# vmstat 5 3
procs ———–memory———- —swap– —–io—- –system– —–cpu——
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 1060608 24372 739080 0 0 1334 63 1018 1571 14 11 66 10 0
0 0 0 995244 24392 799656 0 0 6302 160 1221 1962 10 10 62 18 0
0 0 0 992376 24400 799784 0 0 1 28 992 1886 3 2 95 0 0
#

See the vmstat man page.
–free

Reports the current memory usage. The “-/+ buffers/cache:” line represents the true used and free memory, ignoring the Linux file system cache.

# free
total used free shared buffers cached
Mem: 8178884 4669760 3509124 0 324056 1717756
-/+ buffers/cache: 2627948 5550936
Swap: 10289148 0 10289148
#

–iostat

Reports I/O statistics.

# iostat
Linux 3.2.10-3.fc16.x86_64 (maggie.localdomain) 03/19/2012 _x86_64_(4 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
2.02 0.23 0.51 0.78 0.00 96.46

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 9.23 100.55 62.99 1796672 1125538
dm-0 13.60 100.31 62.99 1792386 1125524
dm-1 0.02 0.08 0.00 1432 0

#

–CPU Usage

See Linux Process Management (ps, top, renice, kill).
sar

On Linux systems sar (System Activity Reporter) is probably one of the simplest and most versatile tools for reporting system utilization including CPU, memory, disk and network activity. It automatically collects system activity statistics when installed using the following command.

# yum install sysstat

The sar command syntax takes the following form.

# sar [options] [interval [count]]

The “options” parameters determine what is reported, which will be discussed later. The “interval” parameter indicates the time interval in seconds between samples. The “count” parameter indicates the number of samples that will be taken before the command ends. If “count” is omitted, the sampling will continue indefinitely. If both “interval” and “count” are omitted, the command will report the values from the 10 minute samples taken since the machine was last restarted.

As seen in the sar man page, there are lots of available options, but some starting points you may find interesting include:

CPU:
Basic CPU: sar [-u] [interval [count]]
Load Average: sar -q [interval [count]]
Memory:
Kernel Paging: sar -B [interval [count]]
Unused Memory: sar -r [interval [count]]
Swap Space: sar -S [interval [count]]
Disk:
Average Disk I/O: sar -b [interval [count]]
Disk I/O: sar -dp [interval [count]]
Network:
Network: sar -n DEV [interval [count]]
Network Errors: sar -n EDEV [interval [count]]

Here is an example of the output from a CPU report.

# sar -u 1 5
Linux 2.6.32-100.0.19.el5 (ol5-112.localdomain) 06/27/2011

03:10:07 PM CPU %user %nice %system %iowait %steal %idle
03:10:08 PM all 0.00 1.01 23.23 75.76 0.00 0.00
03:10:09 PM all 0.00 1.02 35.71 63.27 0.00 0.00
03:10:10 PM all 0.98 3.92 35.29 59.80 0.00 0.00
03:10:11 PM all 0.00 1.03 29.90 69.07 0.00 0.00
03:10:12 PM all 0.00 2.00 35.00 63.00 0.00 0.00
Average: all 0.20 1.81 31.85 66.13 0.00 0.00
#

–mpstat

Reports processor related statistics.

# mpstat 10 2
Linux 2.6.32-100.0.19.el5 (ol5-112.localdomain) 06/27/2011

01:59:57 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
02:00:07 PM all 1.21 0.00 0.90 0.20 0.00 0.00 0.00 97.69 980.50
02:00:17 PM all 0.70 0.00 0.40 0.00 0.00 0.10 0.00 98.79 973.77
Average: all 0.95 0.00 0.65 0.10 0.00 0.05 0.00 98.24 977.14
#

See the mpstat man page.
–top

Displays top tasks.

# top
top – 13:58:17 up 2 min, 1 user, load average: 2.54, 1.11, 0.41
Tasks: 160 total, 6 running, 154 sleeping, 0 stopped, 0 zombie
#

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process.

SELECT a.username,
a.osuser,
a.program,
spid,
sid,
a.serial#
FROM v$session a,
v$process b
WHERE a.paddr = b.addr
AND spid = ‘&pid’;

See the top man page.

–Hide Passwords

You may be required to use passwords in scripts calling Oracle tools, like SQL*Plus, Export/Import and RMAN etc. One method to remove the credentials from the script itself is to create a credentials file to hold them. In this case I’m using “/home/oracle/.scottcred”, which contains the following.

scott/tiger

Change the permissions to make sure the file is only visible to the owner.

$ chmod 600 /home/oracle/.scottcred

Now replace references to the credentials with the contents of the file.

$ expdp < /home/oracle/.scottcred schemas=SCOTT directory=DATA_PUMP_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Alternatively, consider using one of the following:

Secure External Password Store
OS Authentication

–Automatic Startup Scripts on Linux

This text has been replaced by a separate article here.
CRON

See CRON : Scheduling Tasks on Linux.

There are two methods of editing the crontab file. First you can use the “crontab -l > filename” option to list the contents and pipe this to a file. Once you’ve editied the file you can then apply it using the “crontab filename”.

Login as root
crontab -l > newcron
Edit newcron file.
crontab newcron

Alternatively you can use the “crontab -e” option to edit the crontab file directly.

The entries have the following elements.

field allowed values
—– ————–
minute 0-59
hour 0-23
day of month 1-31
month 1-12
day of week 0-7 (both 0 and 7 are Sunday)
user Valid OS user
command Valid command or script.

The first 5 fields can be specified using the following rules.

* – All available values or “first-last”.
3-4 – A single range representing each possible from the start to the end of the range inclusive.
1,2,5,6 – A specific list of values.
1-3,5-8 – A specific list of ranges.
0-23/2 – Every other value in the specified range.

The following entry runs a cleanup script a 01:00 each Sunday. Any output or errors from the script are piped to /dev/null to prevent a buildup of mails to root.

0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1
—-Useful Files

Here are some files that may be of use.
Path Contents
/etc/passwd User settings
/etc/group Group settings for users.
/etc/hosts Hostname lookup information.
/etc/system Kernel parameters for Solaris.
/etc/sysconfigtab Kernel parameters for Tru64.
/etc/sysctl.conf Kernel parameters for Linux.

Advertisements

Migration From SQL Server To Oracle Using SQL Developer: ——————————————————– Migration is the process of copying the schema objects and data from a non-Oracle database, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2, to an Oracle database. To migrate a third-party database to Oracle easily, you can choose the following options using SQL Developer: – Migrating using the Migration Wizard – Copying tables to Oracle Migrating Using the Migration Wizard The Migration wizard provides a screen to manage all the steps needed for the migration to Oracle database. These steps are as follows: – Capturing the source database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2), – Converting it to Oracle format, – Generating DDL, – Performing the conversion. After a brief explanation about the process of migration, let’s do a simple migration from SQL Server database to Oracle database. 1- I created sample SQL Server database named TALIPTEST. 2- The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. For a migration repository create a database connection to convenient Oracle database and give following grants. CREATE USER MIGRATIONS IDENTIFIED BY “migration” DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; grant create session to migrations; grant resource to migrations; grant create view to migrations; For multischema migrations, you must grant the privileges with the ADMIN option as follows. grant resource to migrations with admin option; grant create role to migrations with admin option; grant alter any trigger to migrations with admin option; grant create user to migrations with admin option; 3- Download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html 4- Unzip the file named sqldeveloper-3.2.20.09.87.zip and extract it. Open sqldeveloper.exe file to open SQL Developer. 5- Right click to the “Connections” and click the “New Connections”. 6- Create a database connection named Migration_Repository that connects to the MIGRATIONS user. 7- Right-click the Migration_Repository connection, and select “Migration Repository” menu , then “Associate Migration Repository” to create the repository. 8- Schema objects needed for migration will be created in MIGRATIONS schema. 9- To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download . Extract the dowloaded zip file named jtds-1.2-dist.zip 10- Click “Tools” and then “Prefenrences”. 11- Select “Third Party JDBC Drivers” and click “Add Entry” button to add jTDS driver for SQL Server. 12- Select jar file located in jTDS driver folder. 13- Click “OK” button. 14- Now you can connect to SQL Server or Sybase database using SQL Developer. Connect to the SQL Server database as follows. 15- And finally, let us create the database connection which we will migrate to. 16- The migration wizard is invoked in a variety of contexts. You can right-click a third-party database connection and select “Migrate to Oracle” or you can click “Tools>Migration>Migrate…”. 17- Click “Next” button. 18- Select migration repository database connection. 19- Give a name for migration project and select a directory to write output. 20– Select third party database connection. You can migrate database on online or offline mode. If you choose online migration then Migration Wizard will perform all needed operations. If you choose offline migration then Migration Wizard will generate all needed DDL scripts. 21- Select SQL Server database which we want to migrate to Oracle. 22- Specify the conversion options. And click “Advanced Options” link to ensure “Microsoft SQL Server : Is quoted identifier on” option is selected. 23- Select target database connection. 24- If you select offline migration then offline migration script will be generated in the project output directory. 25- Select the connections to be used for online data move. 26- Click “Finish” button so start migration. 27- Migration and conversion process will be perfomed as follows. Now, Our SQL Server database in Oracle :) Copying tables to Oracle Right click the table located in third party database and click “Copy To Oracle” Select destination (Oracle) database connection and click “Apply” button. If you choose “Include Data” check box it will create table structure and move data. Copy to Oracle database is finished. Note: This method doesn’t move indexes, triggers, etc. It only moves table structure and data.

Migration From SQL Server To Oracle Using SQL Developer:
——————————————————–

Migration is the process of copying the schema objects and data from a non-Oracle database,
such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2, to an Oracle database.

To migrate a third-party database to Oracle easily, you can choose the following options using SQL Developer:

– Migrating using the Migration Wizard

– Copying tables to Oracle

Migrating Using the Migration Wizard

The Migration wizard provides a screen to manage all the steps needed for the migration to Oracle database. These steps are as follows:

– Capturing the source database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2),

– Converting it to Oracle format,

– Generating DDL,

– Performing the conversion.

After a brief explanation about the process of migration, let’s do a simple migration from SQL Server database to Oracle database.

1- I created sample SQL Server database named TALIPTEST.

2- The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. For a migration repository create a database connection to convenient Oracle database and give following grants.

CREATE USER MIGRATIONS IDENTIFIED BY “migration”

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

grant create session to migrations;

grant resource to migrations;

grant create view to migrations;

For multischema migrations, you must grant the privileges with the ADMIN option as follows.

grant resource to migrations with admin option;

grant create role to migrations with admin option;

grant alter any trigger to migrations with admin option;

grant create user to migrations with admin option;

3- Download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

4- Unzip the file named sqldeveloper-3.2.20.09.87.zip and extract it. Open sqldeveloper.exe file to open SQL Developer.

5- Right click to the “Connections” and click the “New Connections”.

6- Create a database connection named Migration_Repository that connects to the MIGRATIONS user.

7- Right-click the Migration_Repository connection, and select “Migration Repository” menu , then “Associate Migration Repository” to create the repository.

8- Schema objects needed for migration will be created in MIGRATIONS schema.

9- To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download . Extract the dowloaded zip file named jtds-1.2-dist.zip

10- Click “Tools” and then “Prefenrences”.

11- Select “Third Party JDBC Drivers” and click “Add Entry” button to add jTDS driver for SQL Server.

12- Select jar file located in jTDS driver folder.

13- Click “OK” button.

14- Now you can connect to SQL Server or Sybase database using SQL Developer. Connect to the SQL Server database as follows.

15- And finally, let us create the database connection which we will migrate to.

16- The migration wizard is invoked in a variety of contexts. You can right-click a third-party database connection and select “Migrate to Oracle” or you can click “Tools>Migration>Migrate…”.

17- Click “Next” button.

18- Select migration repository database connection.

19- Give a name for migration project and select a directory to write output.

20– Select third party database connection. You can migrate database on online or offline mode. If you choose online migration then Migration Wizard will perform all needed operations. If you choose offline migration then Migration Wizard will generate all needed DDL scripts.

21- Select SQL Server database which we want to migrate to Oracle.

22- Specify the conversion options. And click “Advanced Options” link to ensure “Microsoft SQL Server : Is quoted identifier on” option is selected.

23- Select target database connection.

24- If you select offline migration then offline migration script will be generated in the project output directory.

25- Select the connections to be used for online data move.

26- Click “Finish” button so start migration.

27- Migration and conversion process will be perfomed as follows.

Now, Our SQL Server database in Oracle 🙂

Copying tables to Oracle

Right click the table located in third party database and click “Copy To Oracle”

Select destination (Oracle) database connection and click “Apply” button. If you choose “Include Data” check box it will create table structure and move data.

Copy to Oracle database is finished.

Note: This method doesn’t move indexes, triggers, etc. It only moves table structure and data.

Clean Up Oracle archive log using cron ————————————– Cron List Oracle has capability for creating hot backup using archive log mechanism. This feature act like recovery data system for restoring database content to specified time if the original database has force major which make data corrupted. But, this mechanism will suck up the free space of hard disk and typically if we don’t maintain the following problem maybe occured : 1. The Space allocated for archive run out which make all transaction will be suspended 2. Hard disk space will be run out Usually we can delete completed archive log using RMAN command, but this sometimes takes time. Below is automation script for deleting completed archive log using cron. 1. create script (named cleanarchive.sh) #!/bin/bash export ORACLE_BASE=/database/oracle/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export ORACLE_OWNR=oracle export ORACLE_SID=orcl export PATH=$PATH:$ORACLE_HOME/bin su – oracle -c rman target / </database/oracle/crontab/arch.log 2>&1 3. Restart cron service Voila …, now the completed archive log will be checked and cleaned up every day, and we just left the archive less then 7 day till the day executed.

Clean Up Oracle archive log using cron
————————————–
Cron List

Oracle has capability for creating hot backup using archive log mechanism. This feature act like recovery data system for restoring database content to specified time if the original database has force major which make data corrupted.

But, this mechanism will suck up the free space of hard disk and typically if we don’t maintain the following problem maybe occured :

1. The Space allocated for archive run out which make all transaction will be suspended

2. Hard disk space will be run out

Usually we can delete completed archive log using RMAN command, but this sometimes takes time.

Below is automation script for deleting completed archive log using cron.

1. create script (named cleanarchive.sh)

#!/bin/bash
export ORACLE_BASE=/database/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_OWNR=oracle
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin

su – oracle -c rman target / << __EOF__
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE -7’;
EXIT
__EOF__

2. create new job on cron sequence (will be executed everyday at 22:55)

crontab -e

55 22 * * * /bin/sh /database/oracle/crontab/cleanarchive.sh >/database/oracle/crontab/arch.log 2>&1

3. Restart cron service

Voila …, now the completed archive log will be checked and cleaned up every day, and we just left the archive less then 7 day till the day executed.

10g backup and recovery11g_Grid_control_new11gr2-rac-on-aix6-1-0825105300FMcmp1br_test_lossof-datafile_10gcookbook-v2[1].0-10grac r2 – asm – aix5l – san storagedeploying-oracle-11gr2-rac-on-rhel6_1.1expert-oracle-database-architecturegrow_that_dba_career_-_a_wissen_white_paperHowtoBackupandRestoreaRACDatabaseloss-of-all-controlfilesloss-of-all-online-redolog-filesnoarchivelog-database-recovery-via-rmanOracle 11g XE TutorialOracle Database 11g and MySQL 5.5 Developer Handbookoracle_ggOracle+Database+11g+PL+SQL+ProgrammingOracle11gR2-RAC-Solaris10-Iscsi-ZFS-Vmware-RageshMoyanOracle-Database Comparisions of Various Editionsoracle-database-11g-the-complete-referenceOVM-2.2-Server-InstallationPerforming-a-database-clone-using-a-Data-Guard-physical-standby-databasePLSQL Scheduleplsql_tutorialpoint-in-time-recovery-using-rmanpoint-in-time-recovery-via-rmanRAC 11gR2 Setup_v1.2recovery-of-a-dropped-tablespacerecovery-scenario_loss_of_allfilesRefresh-Test-Database-From-Production-Database-Procedurerestore-on-scratch-serverRestoring a ASM backup to non-ASM and restoring from RAC to single-instanceRestoring the SPFILE – with and without AUTOBACKUPrman_datafile_recoveryrman_disater_recoveryrman-duplicate-database-on-the-same-host1RMAN-Recovering-Block-Corruptionrman-recovery-scenarios-related-to-databaseRMAN-recovery-through-RESETLOGSrman-restore-on-another-machine-with-different-file-system-same-database-namerman-tutorialScenario is loss of control files – AUTOBACKUP is enabledScenario is loss of control files – No AUTOBACKUPSQL ReferenceSQL-Commands-1KeydataTROUBLESHOOTINGORACLECLUSTERWAREWaitsinRACworkshop

11g RAC InstallationAdding Node To Oracle RAC DatabaseImplementation of 11gR2 RAC on LinuxOracle Real Application Cluster(RAC) SCAN,DNS,GNSRAC IssuesStep by step DNS Configuration on Linux RHEL5OEL5Oracle Interview Questions and AnswersRAC Setup Documentation

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

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