Database Cloning in Mysql

1. Make a backup/dump of source database
=======================================

C:\Users\nandhsa>mysqldump -u root -p testmysql -r bkuptestmysql1.sql
Enter password: ****


2. Connect to MYSQL prompt
============================
C:\Users\nandhsa>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.5.33 MySQL Community Server (GPL)

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

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

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

3. Create a target database
===========================
mysql> create database newtestmysql;
Query OK, 1 row affected (0.00 sec)

4. Connect to target database
===============================

mysql> use newtestmysql;
Database changed

5. Import the dump/backup of the source database
=================================================
mysql> source bkuptestmysql1.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)



Create a user and give it permissions to the new database:
============================================================
mysql> use newtestmysql
Database changed
mysql> create user testuser identified by 'testuser';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on newtestmysql.* to 'testuser'@'localhost' identified by 'test
user';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Installation of Mysql 5.6 in RHEL 5

 

          1.       Go to below link and provide credentials.

https://edelivery.oracle.com

          2.       Select the product from the list .

                  

   3.   Download the rpm zip file.

         

        4.    Ftp it to the linux machine.

        5.    Once you unzip, you will get the following files.

                 

           

        6.  Next, make sure to have Perl installed in the machine.

        7.   Now, install the package for shared libraries , Server and client 

                

              

      2014-04-11 10:10:50 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2014-04-11 10:10:50 6343 [Note] InnoDB: Using atomics to ref count buffer pool pages

2014-04-11 10:10:50 6343 [Note] InnoDB: The InnoDB memory heap is disabled

2014-04-11 10:10:50 6343 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2014-04-11 10:10:50 6343 [Note] InnoDB: Compressed tables use zlib 1.2.3

2014-04-11 10:10:50 6343 [Note] InnoDB: Using Linux native AIO

2014-04-11 10:10:50 6343 [Note] InnoDB: Not using CPU crc32 instructions

2014-04-11 10:10:50 6343 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2014-04-11 10:10:51 6343 [Note] InnoDB: Completed initialization of buffer pool

2014-04-11 10:10:51 6343 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

2014-04-11 10:10:51 6343 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB

2014-04-11 10:10:51 6343 [Note] InnoDB: Database physically writes the file full: wait...

2014-04-11 10:10:51 6343 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB

2014-04-11 10:10:57 6343 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB

2014-04-11 10:10:58 6343 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2014-04-11 10:10:58 6343 [Warning] InnoDB: New log files created, LSN=45781

2014-04-11 10:10:58 6343 [Note] InnoDB: Doublewrite buffer not found: creating new

2014-04-11 10:10:58 6343 [Note] InnoDB: Doublewrite buffer created

2014-04-11 10:10:58 6343 [Note] InnoDB: 128 rollback segment(s) are active.

2014-04-11 10:10:59 6343 [Warning] InnoDB: Creating foreign key constraint system tables.

2014-04-11 10:10:59 6343 [Note] InnoDB: Foreign key constraint system tables created

2014-04-11 10:10:59 6343 [Note] InnoDB: Creating tablespace and datafile system tables.

2014-04-11 10:10:59 6343 [Note] InnoDB: Tablespace and datafile system tables created.

2014-04-11 10:10:59 6343 [Note] InnoDB: Waiting for purge to start

2014-04-11 10:10:59 6343 [Note] InnoDB: 5.6.17 started; log sequence number 0

2014-04-11 10:10:59 6343 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.

2014-04-11 10:10:59 6343 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.

A random root password has been set. You will find it in '/root/.mysql_secret'.

2014-04-11 10:11:00 6343 [Note] Binlog end

2014-04-11 10:11:00 6343 [Note] InnoDB: FTS optimize thread exiting.

2014-04-11 10:11:00 6343 [Note] InnoDB: Starting shutdown...

2014-04-11 10:11:02 6343 [Note] InnoDB: Shutdown completed; log sequence number 1625977

2014-04-11 10:11:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2014-04-11 10:11:03 6367 [Note] InnoDB: Using atomics to ref count buffer pool pages

2014-04-11 10:11:03 6367 [Note] InnoDB: The InnoDB memory heap is disabled

2014-04-11 10:11:03 6367 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2014-04-11 10:11:03 6367 [Note] InnoDB: Compressed tables use zlib 1.2.3

2014-04-11 10:11:03 6367 [Note] InnoDB: Using Linux native AIO

2014-04-11 10:11:03 6367 [Note] InnoDB: Not using CPU crc32 instructions

2014-04-11 10:11:03 6367 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2014-04-11 10:11:03 6367 [Note] InnoDB: Completed initialization of buffer pool

2014-04-11 10:11:03 6367 [Note] InnoDB: Highest supported file format is Barracuda.

2014-04-11 10:11:03 6367 [Note] InnoDB: 128 rollback segment(s) are active.

2014-04-11 10:11:03 6367 [Note] InnoDB: Waiting for purge to start

2014-04-11 10:11:03 6367 [Note] InnoDB: 5.6.17 started; log sequence number 1625977

2014-04-11 10:11:03 6367 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.

2014-04-11 10:11:03 6367 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.

2014-04-11 10:11:03 6367 [Note] Binlog end

2014-04-11 10:11:03 6367 [Note] InnoDB: FTS optimize thread exiting.

2014-04-11 10:11:03 6367 [Note] InnoDB: Starting shutdown...

2014-04-11 10:11:05 6367 [Note] InnoDB: Shutdown completed; log sequence number 1625987

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !

You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,

no other statement but 'SET PASSWORD' will be accepted.

See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.

This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/my.cnf and

will be used by default by the server when you start it.

You may edit this file to change server settings

8.  Now the installation is completed. Let us some passwords,create users and grant access for the outsiders to access the database.

The default password is located in

/root/.mysql_secret

[root@vm1 ~]# cat /root/.mysql_secret

# The random password set for the root user at Fri Apr 11 10:11:00 2014 (local time): GHUiOJgwkLD7I14V

9. Start the mysql service .

      

     10.    Now, we will login to the database using client utility and change the password. 

    mysql –uroot –p

   type the password from /root/.mysql_secret file

    

     11. Once logged in, we need to change the root password, let us try to run the below query and see.

              

            But its failing. So, we need to use only SET PASSWORD command.

      12.   Let us re-login to Mysql as root user and change the password as ***

mysql> set password for 'root'@'localhost' = PASSWORD('***';

Query OK, 0 rows affected (2.28 sec)

Now, we have set the root password.  

      13.       Next, we will create a super user to access the database from outside server even though it is not a good practice to give access to outside server.

mysql> create user 'rootadmin'@'%' identified by '****';

Query OK, 0 rows affected (0.15 sec)

mysql> grant all on *.* to 'rootadmin'@'%';

Query OK, 0 rows affected (0.01 sec)

14.       As next step, we will check the firewall rules are updated for granting the clients to respond to port 3306 for outside world.  To do this, exit out of mysql  using either ‘exit’ or ‘\q’.

vi /etc/sysconfig/iptables

Add the line.

-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

15. Restart the iptables service.

16.Finally, to enable the mysql to have auto start when the server is rebooted.

Chkconfig mysql  --level  2345 on

17.  Now, let us try to connect from Windows client using Mysql Workbench.

Give the password in “Store in Vault”.

         

MySql for Oracle DBA

Mysql is a database which is widely used for web applications.  Let us have few insights about Mysql server when an Oracle DBA asked to check the Mysql.

Connecting to Mysql

             1.       Login to server using ssh connection.

             2.       Use the below command

mysql –u root

If you get error message “ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)” , then you have to use.

mysql –u –root –p

[root@vm1 etc]# mysql -u root -p

Enter password:

It will prompt for the password for root user. Once password is provided, it will take you to Mysql client.

How to check what is there in database

The concept of database is different in both Mysql server and Oracle.

In Oracle, we will have a database and schemas under the database.

In Mysql server, we will have many databases and in Mysql client, we can use the word schema interchangeably with the word database.

1.       Once we login to the Mysql client, type the below command to see the databases in Mysql server,

show databases;

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| database           |

| mysql              |

| performance_schema |

| test               |

+--------------------+

In Mysql, we will have default databases Information_schema,database,mysql,performance_schema and test.  If you notice any other, then those are the databases created.

2.       To use any particular database , type

use <database_name>;

mysql> use mysql;

Database changed

3.       To see the tables in the database, type.

mysql> show tables;

4.       If you want to see more details about the tables, use the below command.

mysql> show table status;

The above command will show the tables stored in the Engines.  Storage Engines are another concept in Mysql which is not in Oracle.

Most common storage engines are MyISAM and Innodb.

MyISAM storage engine is available as default prior to Mysql 5.5 version. In this storage, the update statements will do table-level locking for read operations.

Innodb Storage Engine is available from Mysql 5.6 as default and this performs similar to Oracle with transaction consistent, multi version concurrency, referential integrity and row-level locking.

5.       SQL statements can be used as follows.

At the end of each statement, we have to either ; or \G . If we give \G, the data will be transformed select * from table_name;

select * from database_name.table_name;

to column format. This is good for tables with many columns.

select * from information_schema.tables  \G

6.       If we need to see the script for creating a table, we can use,

mysql> show create table <table_name>;

or

mysql> show create table <database_name>.<table_name>;

7.       To see the indexes for the table.

mysql> show indexes for <table_name>;

8.       To view the description of the tables, Mysql uses the command similar to oracle describe.

mysql> describe mysql.user

or

mysql> desc mysql.user

  

How many users are connected to the database?

Once you connected to the database, you may want to know how many users are connected to the database.  To list this, use

mysql> show processlist;

This will list the following information for each user.

User – which user is connected.

Host - the machine from which the users are connected

db – which database

Query – which commands the users are currently executing.

How the data is stored in OS

We have seen how the data is stored in the database in Mysql. Now, we will see how the data is stored at the OS level like data files, etc.

The default location of the mysql datafiles is /var/lib/mysql.

If you don’t find any files under this, just open to /etc/my.cnf and check for the path of the data directory  “datadir”.

In this article, we have files under default location /var/lib/mysql

The files for each database will have a directory with their names, here, we have

test, performance_schema,mysql,database

Once you go into the directory for the database, we will have following.

*.MYD – For tables in MyISAM engine.

*.MYI -  For indexes in MyISAM engine.

*.frm – this is the file that describes the structure of each table.

InnoDB tables are stored in InnoDB tablespace which consists of one or more files. This is similar to Oracle tablespaces.  By default, tables and indexes in InnoDB are stored in one tablespace consisting of one file /var/lib/mysql/ibdata1.

Similar to Oracle redo logs, we have logs in Innodb  that allows instance crash recovery.  By default we will have 2 files /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1.  The tablespace files also contain the Undo data.

Data Dictionary in Mysql

Similar to Oracle where we have data dictionary information, we will have tables (actually views) which contain the details of the database like tables, indexes, user privileges, etc., in Mysql.

This information is stored in information_schema database/schema.  This database/schema is available from Mysql 5.0.

mysql> use information_schema;

Database changed

mysql>

mysql> show tables;

mysql> select * from tables where table_name=’<table_name>’;

To know the users created in the database and their purpose

User details are stored in mysql.user table. It also contains all the global privileges the users contains.

select * from mysql.user;

By default, Root user does not have any password and also we have an anonymous user with no password.  To make Mysql server secure and clear few anonymous users with few default security holes, it is recommended to run the script shown below from the operating system. 

mysql_secure_installation

Note: Do not use Mysql client for this.

In Mysql, the users are uniquely identified with the combination of both user and host.

eg. Suppose if we have 2 users san@host1 and san@host2, the two users are different, they can different password and different privileges.

Moreover, in Mysql, the user can connect only from the host defined in mysql.user table. i.e., if any of the users mentioned in our example tries to connect from host3, then the access will be denied.

Now, let us see how to create a user in Mysql and provide  privileges to the user.

mysql> create user testuser@10.10.1.10 identified by 'testuser';

Query OK, 0 rows affected (6.59 sec)

mysql> grant all on mysql.* to testuser@10.10.1.10;

Query OK, 0 rows affected (2.65 sec)

mysql> grant all on test.* to testuser@10.10.1.10;

Query OK, 0 rows affected (0.00 sec)

To see the database level privileges for, we can use the query

select * from mysql.db;

or

select * from mysql.db \G

If you want the user to connect from anywhere, then we have to use the wildcard (%)

mysql> create user testuser1@'%' identified by 'testuser1';

Query OK, 0 rows affected (12.03 sec)

We can even combine the creation and providing privileges in a single statement as below.

mysql> grant all on mysql.* to testuser2@'%' identified by 'testuser2';

Query OK, 0 rows affected (1.47 sec)

mysql> select host,user from mysql.user where user like 'testuse%';

+------------+-----------+

| host       | user      |

+------------+-----------+

| %          | testuser1 |

| %          | testuser2 |

| 10.10.1.10 | testuser  |

+------------+-----------+

3 rows in set (0.00 sec)

How to start and stop Mysql

Mysql database can be started and stopped like normal services in unix.

[root@vm1 ~]# service mysql stop

Shutting down MySQL.......^[ ...                           [  OK  ]

[root@vm1 ~]# service mysql start

[root@vm1 ~]# service mysql status

[root@vm1 ~]# ps -ef |grep mysql

root      6777     1  0 19:48 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/vm1.pid

mysql     6887  6777  2 19:48 pts/1    00:00:03 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/vm1.err --pid-file=/var/lib/mysql/vm1.pid

root      7153  6216  0 19:51 pts/1    00:00:00 grep mysql

mysqld – Mysql server processes

mysqld_safe – process that monitors the Mysql server process and will restart if it has failed.

By default, error logs are stored in /var/lib/mysql/<server_name>.err

Configuration file similar to oracle’s pfile or spfile

Mysql has a file similar to pfiel or spfile and usually it will be in either of the below locations.  (my.ini in windows)

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

In my case, it is /usr/etc/my.cnf. In this file, we have different labeled with Mysqld and Mysql.

Mysqld – it is the section which has configuration data for Mysql server.

Mysql – it is the section which has configuration data for Mysql client.

Note: In case, if you are not able find the /etc/my.cnf path , then use mysql --help and get the path of default options.