Tag: MySQL

Backup and Restore in MySQL

Backup and Restore MySQL Database
·        Mysql include mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT intosql-statements of the source database. To restore the database, execute the *.sql file on destination database.
Backup and Restore command line syntax
·        backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
·        restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
   1>    Backup a single database:
          # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
   2>    Backup multiple database:
               # mysqldump -u root -p[root_password] [database_name] [database_name_2] > dumpfilename.sql
   3>    Verify the dumpfilename.sql dump file contains both the database backup
         # grep -i “Current database:” /tmp/dumpfilename.sql
           Current Database: `database_name`
           Current Database: `database_name_2`
   4>    Backup all the databases:
         # mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql
   5>    Backup a specific table:
         # mysqldump -u root -ptmppassword database1 accounts_contacts /tmp/database1_accounts_contacts.sql
    6>    Different mysqldump group options:
–opt is a group option, which is same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. opt is enabled by default, disable with –skip-opt.
–compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks
How to Restore MySQL database
    1>    Restore a database: When you are restoring the dumpfilename.sql on a remote database, make         sure to create the database before you can perform the restore.
# mysql -u root –ptmppassword
# mysql> create database database_name;
# mysql -u root -ptmppassword database_name < /tmp/dumpfilename.sql
    2>    Backup a local database and restore to remote server using single command: if you want to         create read only database in remote computer. For that you need to create database in remote               computer and then run below command.
[local-server]# mysqldump -u root -ptmppassword database_name  | mysql  \   -u root -ptmppassword –host=remote-server -C database_name1

MySQL Replication

How to Set Up Master Slave Replication in MySQL

This tutorial will use the following IP addresses:
12.34.56.789- Master Database
12.23.34.456- Slave Database

Step One—Configure the Master Database

/etc/mysql/my.cnf
Once inside that file, we need to make a few changes.
The first step is to find the section that looks like this, binding the server to the local host:
bind-address            = 127.0.0.1
Replace the standard IP address with the IP address of server.
bind-address            = 12.34.56.789
Make sure this line is uncommented.
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase
Restart MySQL.
sudo service mysql restart
The next steps will take place in the MySQL shell, itself.
Open up the MySQL shell.
mysql -u root -p
We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;

Follow up with:
FLUSH PRIVILEGES;
The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.
In your current tab switch to “newdatabase”.
USE newdatabase;
Following that, lock the database to prevent any new changes:
FLUSH TABLES WITH READ LOCK;
Then type in:
SHOW MASTER STATUS;
You will see a table that should look something like this:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.
If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.
Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).
mysqldump -u root -p --opt newdatabase > newdatabase.sql
Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.
UNLOCK TABLES;
QUIT;
Now you are all done with the configuration of the the master database.

Step Two—Configure the Slave Database

Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.
Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):
CREATE DATABASE newdatabase;
EXIT;
Import the database that you previously exported from the master database.
mysql -u root -p newdatabase < /path/to/newdatabase.sql
Now we need to configure the slave configuration in the same way as we did the master:
sudo nano /etc/mysql/my.cnf
We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.
server-id               = 2
Following that, make sure that your have the following three criteria appropriately filled out:
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase
You will need to add in the relay-log line: it is not there by default. Once you have made all of the necessary changes, save and exit out of the slave configuration file.
Restart MySQL once again:
sudo service mysql restart
The next step is to enable the replication from within the MySQL shell.
Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

CHANGE MASTER TO MASTER_HOST=’12.34.56.789′,MASTER_USER=’slave_user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=  107;

This command accomplishes several things at the same time:
1. It designates the current server as the slave of our master server.
2. It provides the server the correct login credentials
3. Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.
With that—you have configured a master and slave server.
 Activate the slave server:
START SLAVE;
You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.
SHOW SLAVE STATUS\G
If there is an issue in connecting, you can try starting slave with a command to skip over it:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Database Optimization

OPTIMIZE LARGE DATABASE

For large table and large db we need to change below parameters mostly to optimize it. It is not fixed for each server so you need to set it according to your server requirements.

key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.
key_buffer_size = 128M
innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.
innodb_additional_mem_pool_size = 20M
innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.
innodb_log_file_size = 64M
innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.
table_cache = 2048
thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.
thread_cache_size = 8
query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.
query_cache_size= 16M

MySQL Replication

How to Set Up Master Slave Replication in MySQL

This tutorial will use the following IP addresses:
12.34.56.789- Master Database
12.23.34.456- Slave Database

Step One—Configure the Master Database

/etc/mysql/my.cnf
Once inside that file, we need to make a few changes.
The first step is to find the section that looks like this, binding the server to the local host:
bind-address            = 127.0.0.1
Replace the standard IP address with the IP address of server.
bind-address            = 12.34.56.789
Make sure this line is uncommented.
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase
Restart MySQL.
sudo service mysql restart
The next steps will take place in the MySQL shell, itself.
Open up the MySQL shell.
mysql -u root -p
We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;

Follow up with:
FLUSH PRIVILEGES;
The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.
In your current tab switch to “newdatabase”.
USE newdatabase;
Following that, lock the database to prevent any new changes:
FLUSH TABLES WITH READ LOCK;
Then type in:
SHOW MASTER STATUS;
You will see a table that should look something like this:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.
If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.
Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).
mysqldump -u root -p --opt newdatabase > newdatabase.sql
Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.
UNLOCK TABLES;
QUIT;
Now you are all done with the configuration of the the master database.

Step Two—Configure the Slave Database

Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.
Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):
CREATE DATABASE newdatabase;
EXIT;
Import the database that you previously exported from the master database.
mysql -u root -p newdatabase < /path/to/newdatabase.sql
Now we need to configure the slave configuration in the same way as we did the master:
sudo nano /etc/mysql/my.cnf
We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.
server-id               = 2
Following that, make sure that your have the following three criteria appropriately filled out:
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase
You will need to add in the relay-log line: it is not there by default. Once you have made all of the necessary changes, save and exit out of the slave configuration file.
Restart MySQL once again:
sudo service mysql restart
The next step is to enable the replication from within the MySQL shell.
Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

CHANGE MASTER TO MASTER_HOST=’12.34.56.789′,MASTER_USER=’slave_user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=  107;

This command accomplishes several things at the same time:
1. It designates the current server as the slave of our master server.
2. It provides the server the correct login credentials
3. Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.
With that—you have configured a master and slave server.
 Activate the slave server:
START SLAVE;
You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.
SHOW SLAVE STATUS\G
If there is an issue in connecting, you can try starting slave with a command to skip over it:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Database Optimization

OPTIMIZE LARGE DATABASE

For large table and large db we need to change below parameters mostly to optimize it. It is not fixed for each server so you need to set it according to your server requirements.

key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.
key_buffer_size = 128M
innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.
innodb_additional_mem_pool_size = 20M
innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.
innodb_log_file_size = 64M
innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.
table_cache = 2048
thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.
thread_cache_size = 8
query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.
query_cache_size= 16M

Create Multiple MySQL Instances

How to Create Multiple MySQL Instance
Step 1: Create a new MySQL data directory.
[root@server ~]# mkdir -p /var/lib/mysql2
Step 2: Now give permission and ownership .Here we are taking ownership and permission reference from original /var/lib/mysql data directory.
Note: /var/lib/mysql is by default data directory path
[root@server ~]# chmod –reference /var/lib/mysql /var/lib/mysql2
[root@server ~]# chown –reference /var/lib/mysql /var/lib/mysql2
Step 3: Now create a new my2.cnf file and paste the below given contents. We will run the new mysql instance in port no. 3337.
Copy the my.cnf file and make it blank
[root@server ~]# cp -r /etc/my.cnf /etc/my2.cnf
Now edit with vi editor or your favorite editor.
[root@server ~]# vi /etc/my2.cnf
[mysqld]
datadir=/var/lib/mysql2
socket=/var/lib/mysql/mysql2.sock
port=3337
[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid
Step 4: Now install the database in the new mysql data directory
Below given is the output from my server (as a Reference)
[root@server ~]# mysql_install_db –user=mysql –datadir=/var/lib/mysql2
Installing MySQL system tables…
OK
Filling help tables…
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h localhost.localdomain password ‘new-password’
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
[root@server ~]#


Step 5: Now the new instance is ready. Now in below section we will learn how to start, stop the mysql instance and how to connect with it.
Note: new mysql instance is running in port no. 3337 as per this tutorial
To start new mysql instance, use below given command
[root@server ~]# mysqld_safe –defaults-file=/etc/my2.cnf &
Now check the port no. 3337 is listening or not
[root@server ~]# netstat -anp |grep 3337
Step 6: Edit the init file and make some minor changes to make it this instance specific [Four edits required]
[root@server ~]# vi /etc/init.d/mysql2
Edit 1: Add the following line after line 138 for the init script to handle ports
–port=*)     port=`echo “$arg” | sed -e ‘s/^[^=]*=//’` ;;
Edit 2: At line 215 update my.cnf to point to the new config file my2.cnf for this instance
conf=/etc/my2.cnf
Edit 3: At line 257 add -c flag to the arguments to read this config file while parsing server arguments
parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server -c/etc/my2.cnf`
Edit 4: Add port argument to mysql_safe command on line 284
$bindir/mysqld_safe –defaults-file=/etc/my2.cnf –datadir=”$datadir” –pid-file=”$mysqld_pid_file_path” –port=”$port” –socket=”$datadir”/mysql2.sock $other_args >/dev/null 2>&1 &
To connect new mysql instance
Syntax: mysql -u username -p -h 127.0.0.1 -P port-no
As per our tutorial:
mysql -u root -p -h 127.0.0.1 -p 3337
It will ask password, hit enter because root password is blank
Now set root password (It is up to your choice, for security reason it is recommended).You can see mysql prompt.
mysql > use mysql;
mysql > update user set password=PASSWORD(“Give-here-new-root-PASSWORD”) where User=’root’;
mysql > flush privileges;
mysql > exit
Recheck if MySQL root password is applied or not
mysql -u root -p -h 127.0.0.1 -p 3337
To stop new mysql instance use below given command
mysqladmin -S /var/lib/mysql/mysql2.sock shutdown -p

Create Multiple MySQL Instances

How to Create Multiple MySQL Instance
Step 1: Create a new MySQL data directory.
[root@server ~]# mkdir -p /var/lib/mysql2
Step 2: Now give permission and ownership .Here we are taking ownership and permission reference from original /var/lib/mysql data directory.
Note: /var/lib/mysql is by default data directory path
[root@server ~]# chmod –reference /var/lib/mysql /var/lib/mysql2
[root@server ~]# chown –reference /var/lib/mysql /var/lib/mysql2
Step 3: Now create a new my2.cnf file and paste the below given contents. We will run the new mysql instance in port no. 3337.
Copy the my.cnf file and make it blank
[root@server ~]# cp -r /etc/my.cnf /etc/my2.cnf
Now edit with vi editor or your favorite editor.
[root@server ~]# vi /etc/my2.cnf
[mysqld]
datadir=/var/lib/mysql2
socket=/var/lib/mysql/mysql2.sock
port=3337
[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid
Step 4: Now install the database in the new mysql data directory
Below given is the output from my server (as a Reference)
[root@server ~]# mysql_install_db –user=mysql –datadir=/var/lib/mysql2
Installing MySQL system tables…
OK
Filling help tables…
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h localhost.localdomain password ‘new-password’
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
[root@server ~]#


Step 5: Now the new instance is ready. Now in below section we will learn how to start, stop the mysql instance and how to connect with it.
Note: new mysql instance is running in port no. 3337 as per this tutorial
To start new mysql instance, use below given command
[root@server ~]# mysqld_safe –defaults-file=/etc/my2.cnf &
Now check the port no. 3337 is listening or not
[root@server ~]# netstat -anp |grep 3337
Step 6: Edit the init file and make some minor changes to make it this instance specific [Four edits required]
[root@server ~]# vi /etc/init.d/mysql2
Edit 1: Add the following line after line 138 for the init script to handle ports
–port=*)     port=`echo “$arg” | sed -e ‘s/^[^=]*=//’` ;;
Edit 2: At line 215 update my.cnf to point to the new config file my2.cnf for this instance
conf=/etc/my2.cnf
Edit 3: At line 257 add -c flag to the arguments to read this config file while parsing server arguments
parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server -c/etc/my2.cnf`
Edit 4: Add port argument to mysql_safe command on line 284
$bindir/mysqld_safe –defaults-file=/etc/my2.cnf –datadir=”$datadir” –pid-file=”$mysqld_pid_file_path” –port=”$port” –socket=”$datadir”/mysql2.sock $other_args >/dev/null 2>&1 &
To connect new mysql instance
Syntax: mysql -u username -p -h 127.0.0.1 -P port-no
As per our tutorial:
mysql -u root -p -h 127.0.0.1 -p 3337
It will ask password, hit enter because root password is blank
Now set root password (It is up to your choice, for security reason it is recommended).You can see mysql prompt.
mysql > use mysql;
mysql > update user set password=PASSWORD(“Give-here-new-root-PASSWORD”) where User=’root’;
mysql > flush privileges;
mysql > exit
Recheck if MySQL root password is applied or not
mysql -u root -p -h 127.0.0.1 -p 3337
To stop new mysql instance use below given command
mysqladmin -S /var/lib/mysql/mysql2.sock shutdown -p