MySQL and Oracle command equivalents

MySQL has specific commands, which provides the easy access to the information_schema database, to get the schema level details. But oracle does not provide such easy access to some of the schema level meta data.

Here are  some MySQL specific commands/Syntaxes & equivalent Oracle techniques:

To get the list of databases

MySQL :

show databases

Oracle :

SELECT username FROM all_users ORDER BY username;

To get the current schema

MySQL :

select DATABASE();

Oracle :

SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

To get the list of tables within the current database

MySQL :

use database_name;
show tables;

Oracle :

select * from user_tables;

Here schema is based on the connected username, so it is selected during the creation of the connection.
USER_TABLES will have a row for every table in your schema. If you are looking for the tables in your schema, this would be the correct query. If you are looking for the tables in some other schema, this is not the right table to use.

ALL_TABLES will have a row for every table you have access to regardless of schema. You would, presumably, want to qualify the query by specifying the name of the schema you are interested in, i.e.

SELECT table_name
  FROM all_tables
 WHERE owner = <<name of schema>>

Of course, that assumes that you have at least SELECT access on every table in that schema. If that is not the case, then you would need to use DBA_TABLES (which would require that the DBA grant you access to that table), i.e.

SELECT table_name
  FROM dba_tables
 WHERE owner = <<name of schema>>

To get the connected connection info

MySQL :

show processlist

Oracle :

SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER';

To limit the selection

MySQL :

select * from user limit 10;

Oracle :

select * from table_name where ROWNUM <= 10;

To select rows which is somewhere middle

MySQL :

select username from user limit 10, 15;

Oracle :

select element_name from (select element_name, ROWNUM as row_number from table_name) as t1 where t1.row_number > 10 and t1.row_number <= 15; 

Note: Here we have to use SubQuery rather than call it directly as "select element_name from table_name as t1 where ROWNUM > 10 and ROWNUM <= 15;". This cannot be done as these ROWNUMs are assigned once they are satisfied the given conditions, which follows the WHERE. Since condition "ROWNUM > 10 and ROWNUM <= 15" will never be satisfied from the start ROWNUMs will never be incremented. So we need to use the Subqueries to let the ROWNUMs assigned within the Subquery and later filter the required results from the outside query.

Describe table has a same syntax in both MySQL & Oracle.

desc table_name;

To view errors/warnings

MySQL :

show warings / show errors

Oracle :

select * from user_errors;/ show errors

MySQL has auto_increment Columns

MySQL :

create table table_name (element_id int AUTO_INCREMENT primary, element_name varchar(20));

Oracle :
i) Create table without the auto_increment keywords (because it does not exist in Oracle)

create table table_name (element_id int primary, element_name varchar(20));

ii) Create a sequence, which provides the incremented values

create sequence auto_incrementor;

iii) Create a trigger, which gets the next value from the sequence and updates it to the column to be auto_incremented

CREATE TRIGGER trig_incrementor BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
SELECT auto_incrementer.NEXTVAL into :new.element_id FROM dual;
END;

To get the table create script back

MySQL :

show create table table_name;

Oracle :
Make sure that the select_catalog_role is already available for the given user if not assign the role, as shown below.
grant select_catalog_role to [username];Increase the page size and maximum width for displaying the results so that complete table definition can be displayed in the sqlplus console.

set pagesize 999
set long 9000
select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', 'database_name') from dual;

To get the session variables

MySQL :

show variables; or show variables like 'inno%';

Oracle :

SELECT name, value FROM gv$parameter; or SELECT sys_context('USERENV', ) FROM dual;

Explain the execution plan of a sql statement

MySQL :

explain select * from table_name;

Oracle :

i) First execute the explain plan so that it will fill the plan_table (this table need to be created according to the standard plan_table format, if it does not exist already)

explain plan select * from table_name;

ii) Now the results of the explain plan will be populated in the plan_table, so we need to use a row connecting query to get a readable summary of the results.

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id;

MySQL and Postgres command equivalents

Task: list existing databases, connect to one of the databases, then list existing tables and finally show the structure of one of the tables.

In Mysql:

show databases;
use database;
show tables;
describe table;
exit

In PostgreSQL:

\l
\c database
\dt
\d+ table
\q

Get list of foreign keys in MySQL

Here's a simple query for displaying all foreign keys and their references in a MySQL DB:

select
concat(table_name, '.', column_name) as 'foreign key',
concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
information_schema.key_column_usage
where
referenced_table_name is not null;

MySQL Export to CSV

If you need the data from a table or a query in a CSV fiel so that you can open it on any spreadsheet software, like Excel you can use something like the following:

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1

Or you can use sed:

mysql -u username -ppassword database -B -e "SELECT * FROM table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Explanation:

username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export

The -B option will delimit the data using tabs and each row will appear on a new line.
The -e option denotes the MySQL command to run, in our case the "SELECT" statement.
The "sed" command used here contains three sed scripts:

s/\t/","/g;s/^/"/ - this will search and replace all occurences of 'tabs' and replace them with a ",".

s/$/"/; - this will place a " at the start of the line.

s/\n//g - this will place a " at the end of the line.

You can find the exported CSV file in the current directory. The name of the file is filename.csv.

However if there are a lot of tables that you need to export, you'll need a script like this:

#!/bin/bash
#### Begin Configuration ####
DB="mydb"
MYSQL_USER="root"
MYSQL_PASSWD='mypass'
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL="/usr/bin/mysql"
#### End Configuration ####
MYSQL_CMD="$MYSQL -u $MYSQL_USER -p$MYSQL_PASSWD -P $MYSQL_PORT -h $MYSQL_HOST"
TABLES=`$MYSQL_CMD --batch -N -D $DB -e "show tables"`
for TABLE in $TABLES
do
SQL="SELECT * FROM $TABLE;"
OUTFILE=$TABLE.csv
$MYSQL_CMD --database=$DB --execute="$SQL" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > $OUTFILE
done

Just be sure to change the configuration section to meet your needs.
Name the file something like: export_csv.sh and be sure to make it executable. In Linux, do something like:

chmod +x ./export_csv.sh

If you want to have all of the exported files in a certain directory, you could either modify the script or just make the cirectory, "cd" into it, and then run the script. It assumes you want to create the files in the current working directory.
To change that behavior, you could easily modify the "OUTFILE" variable to something like:

OUTFILE="/my_path/$TABLE.csv"

Drop all tables in a MySQL database

If you whant to drop all tables from one MySQL DB without droping the DB, you can use this command:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

Backuppc and MySQL

The best way to backup a MySql Server using Backuppc is to use a pre-dump script.

you can use $Conf{DumpPreUserCmd} to issue a MysqLDump

Stdout from these commands will be written to the Xfer (or Restore) log file, note that all Cmds are executed directly without a shell, so the prog name needs to be a full path and you can't include shell syntax like redirection and pipes; put that in a script if you need it.

So in our case we would create a script, on the Backuppc client, to dump all databases into a file:

vi /usr/local/sbin/myBkp.sh

and paste the following into it:

#!/bin/bash
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
DEST="/backup/mysqlDump.sql"
MYSQLUSER="root"
MYSQLPASS="mypassword"
# no need to change anything below...
#####################################################
LOCKFILE=/tmp/myBkup.lock
if [ -f $LOCKFILE ]; then
echo "Lockfile $LOCKFILE exists, exiting!"
exit 1
fi
touch $LOCKFILE
echo "== MySQL Dump Starting $(date) =="
$MYSQLDUMP --single-transaction --user=${MYSQLUSER} --password="${MYSQLPASS}" -A > ${DEST}
echo "== MySQL Dump Ended $(date) =="
rm $LOCKFILE

make the script executable:

chmod +x /usr/local/sbin/myBkp.sh 

and set $Conf{DumpPreUserCmd} with:

$sshPath -q -x -l root $host /usr/local/sbin/myBkp.sh

Now you just have to make shure that Backuppc is getting the /backup folder (or whatever folder you have set in the script) and you can also exclude the /var/lib/mysql folder from backuppc backups.

MySQL - Converting to Per Table Data File for InnoDB

Issue with shared InnoDB /var/lib/mysql/ibdata1 storage
InnoDB tables currently store data and indexes into a shared tablespace (/var/lib/mysql/ibdata1). Due to the shared tablespace, data corruption for one InnoDB table can result in MySQL failing to start up on the entire machine. Repairing InnoDB corruption can be extremely difficult to perform and can result in data loss for tables that were not corrupted originally during that repair process.

Since MySQL 5.5 will be using InnoDB as the default storage engine, it is important to consider the consequences of continuing to utilize the shared tablespace in /var/lib/mysql/ibdata1Changing to per-table tablespace with innodb_file_per_table

As an option to resolve the issue, MySQL has a configuration variable called innodb_file per_table. To use this variable, the following could be placed into /etc/my.cnf to convert InnoDB to a per table file for each InnoDB engine table:

innodb_file_per_table=1

After adding the line, MySQL would need to be restarted on the machine.
The result for using that line in /etc/my.cnf would cause any databases after the line is added to create .idb files in /var/lib/mysql/database/ location. Please note that the shared tablespace will still hold internal data dictionary and undo logs.

Converting old InnoDB tables
Any old databases with InnoDB tables set to previously share the tablespace in ibdata1 will still be using that file, so those old databases would need to be switched to the new system. The following command in MySQL CLI would create a list of InnoDB engine tables and a command to run for each to convert them to the new innodb_file_per_table system:

select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') as command FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';

An example for Roundcube on my test machine shows the following return upon running the prior command:

alter table roundcube.cache ENGINE=InnoDB;
alter table roundcube.contacts ENGINE=InnoDB;
alter table roundcube.identities ENGINE=InnoDB;
alter table roundcube.messages ENGINE=InnoDB;
alter table roundcube.session ENGINE=InnoDB;
alter table roundcube.users ENGINE=InnoDB;

You would then simply need to issue the commands noted by MySQL CLI to then covert each table to the new innodb_file_per_table format.

Please note that these commands would only need to be run in MySQL command line for the conversion.

You can use the following script:

#!/bin/bash
MYSQL="$(which mysql)"
MYSQLUSER="root"
MYSQLPASS="mypassword"
# no need to change anything below...
#####################################################
TBLS=$(mysql -u $MYSQLUSER -p$MYSQLPASS -Bse "select concat(TABLE_SCHEMA ,'.',table_name) as tbl FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';")
for tbl in $TBLS
do
echo "Converting table $tbl"
mysql -u $MYSQLUSER -p$MYSQLPASS -Bse "alter table $tbl ENGINE=InnoDB;"
done

Possible Issues for Converting Old InnoDB Tables
1. Possible system load might occur during the conversion
2. Possible issues with drive space filling up for the conversion

How to determine type of mysql database

To determine the storage engine being used by a table, you can use show table status. The Engine field in the results will show the database engine for the table. Alternately, you can select the engine field from information_schema.tables.

To get the type per database:

mysql -u root -p'<password>' -Bse 'select distinct table_schema, engine from information_schema.tables'

For a specific table use:

select engine from information_schema.tables where table_schema = 'schema_name' and table_name = 'table_name'

You can change between storage engines using alter table:

alter table the_table engine = InnoDB;

Where, of course, you can specify any available storage engine.

Performance Tuning MySQL for Zabbix

On my previous post I've shared some tips on how to tune ZABBIX configuration to get better results,however the most important tunning you have to do is to the data base server. Remember that this values depend on how much memory you have available on your server, here is how I've configured my MySQL server:

1. use a tmpfs tmpdir, create a folder like /mytmp and In /etc/my.cnf configure:

tmpdir=/mytmp

in /etc/fstab i put:

tmpfs /mytmp tmpfs size=1g,nr_inodes=10k,mode=700,uid=102,gid=105 0 0

You'll have to mkdir /mytmp and the numeric uid and gid values for your mysql user+group need to go on that line. Then you should be able to mount /mytmp and use tmpfs for mysql's temp directory. I don't know about the size and nr_inodes options there, I just saw those in linux tmpfs docs on the web and they seemed reasonable to me.

2. Buffer cache/pool settings.

In /etc/my.cnf jack up innodb_buffer_pool_size as much as possible. If you use /usr/bin/free the value in the "+/- buffer cache" row under the "free" column shows you how much buffer cache you have. I've also setup innodb to use O_DIRECT so that the data cached in the innodb buffer pool would not be duplicated in the filesystem buffer cache. So, in /etc/my.cnf:

innodb_buffer_pool_size=8000M
innodb_flush_method=O_DIRECT

3. Size the log files.

The correct way to resize this is documented here:

http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

In /etc/my.cnf the value I'm going to try is:

innodb_log_file_size=64M

A too small value means that MySQL is constantly flushing from the logfiles to the table spaces. It is better to increase this size on write-mostly databases to keep zabbix streaming to the logfiles and not flushing into the tablespaces constantly. However, the penalty is slower shutdown and startup times.

4. other parameters

innodb_file_per_table

Use file_per_table to keep tablespaces more compact and use "optimize table" periodically. And when you set this value in my.cnf you don't get an actual file_per_table until you run an optimize on all the tables. This'll take a long time on the large zabbix history* and trends* tables.
Turn on slow query logging:

log_slow_queries=/var/log/mysql.slow.log

This setting seems to affect the hit rate of Threads_created per Connection.

thread_cache_size=4

query_cache_limit=1M
query_cache_size=128M
tmp_table_size=256M
max_heap_table_size=256M
table_cache=256
max_connections = 400
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k 

This should help a lot for high volume writes.

innodb_flush_log_at_trx_commit=2

Duplicate a MySQL Database

Here is a simple script to duplicate a MySQL database:

mysqladmin create new_DB_name -u DB_user --password=DB_pass && \
mysqldump -u DB_user --password=DB_pass DB_name | mysql -u DB_user --password=DB_pass -h DB_host new_DB_name

MySQL Create DB

Crete a DB | schema:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

Create Uer:

CREATE USER 'username'@'%' IDENTIFIED BY 'mypass';

Permissions:

GRANT ALL ON db_name.* TO username @'%' IDENTIFIED BY 'mypass';

Note: Users 'User'@'%' and 'User'@'localhost' are not the same.

MySQL Dump and Restore

DUMP:

All databases:

mysqldump --user=XXXXXXXX --password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL

Just some DB:

mysqldump --user=XXXXXXXX --password=XXXXXXX --databases DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.SQL

Just some tables:

mysqldump --user=XXXXXXXX --password=XXXXXXXX --databases DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

NOTE: if you get and error saying "(...)when using LOCK TABLES" append this to the dump command:

--single-transaction

RESTORE:

mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

Permissions:

GRANT ALL ON db_base.* TO db_user @'%' IDENTIFIED BY 'db_passwd';

Change User password:

$ mysql -u root -p

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-PASSWORD-HERE") where User='tom';

Find and replace in MySQL

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by a new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string, text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’); 

update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’);

The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.

Another example:

SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);

Above statement will return ‘WwWwWw.mysql.com’ as result.

Recover MySQL root Password

Here are the commands you need to type for each step (login as the root user or use sudo):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &

The output should be something like:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root

Output:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+ Done mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p