MySQL Installation-:
Following packages are needed for the MySQL installation.
MySQL-shared-community-5.0.92-1.rhel5
MySQL-devel-community-5.0.92-1.rhel5
MySQL-test-community-5.0.92-1.rhel5
MySQL-client-community-5.0.92-1.rhel5
MySQL-server-community-5.0.92-1.rhel5
Creating password for mysql user-:
Syntax-: mysqladmin –u username –p ‘password’
Use the above command for creating password for a new user.
Changing existing password-:
Syntax-: mysqladmin –u username –p password new-password
If we execute the above command then it asks as Enter password: then we need to give current(old) password.
Output-:
[root@localhost ~]# mysqladmin -u root -p password redhat
Enter password:
[root@localhost ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
To see privileges-:
Syntax-: show grants for username@’%’;
Shows the privileges which are given for the user.
Output-:
mysql> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Taking backup with data-:
Syntax-: mysqldump –u username –ppassword databasename > dumpfile.sql
By using the above command we can take backup of a database including the table structure and data.The above command is used in the linux shell not in the mysql. dumpfile.sql is the name which we provided for the backup. .sql extension should be given while taking the backup.
Output-: Before taking the backup
[root@localhost ~]# ll
total 60
-rw------- 1 root root 1137 Jul 15 16:19 anaconda-ks.cfg
drwxr-xr-x 2 root root 4096 Jul 16 10:16 Desktop
-rw-r--r-- 1 root root 29623 Jul 15 16:19 install.log
-rw-r--r-- 1 root root 3671 Jul 15 16:18 install.log.syslog
[root@localhost ~]#
Output-: After taking the backup
[root@localhost ~]# mysqldump -u root -predhat mysql > mysql.sql
[root@localhost ~]# ll
total 540
-rw------- 1 root root 1137 Jul 15 16:19 anaconda-ks.cfg
drwxr-xr-x 2 root root 4096 Jul 16 10:16 Desktop
-rw-r--r-- 1 root root 29623 Jul 15 16:19 install.log
-rw-r--r-- 1 root root 3671 Jul 15 16:18 install.log.syslog
-rw-r--r-- 1 root root 482938 Jul 23 18:29 mysql.sql
Taking backup without data-:
Syntax-: mysqldump –u username –ppassword --no-data databasename > dumpfile.sql
In the above syntax –no-data is specified to take only the table structure as the backup. In the below output observe the change in size of the backup for the verification after including --no-data.
Ouput-: with data(same as above example)
[root@localhost ~]# mysqldump -u root -predhat mysql > mysql.sql
[root@localhost ~]# ll
total 540
-rw------- 1 root root 1137 Jul 15 16:19 anaconda-ks.cfg
drwxr-xr-x 2 root root 4096 Jul 16 10:16 Desktop
-rw-r--r-- 1 root root 29623 Jul 15 16:19 install.log
-rw-r--r-- 1 root root 3671 Jul 15 16:18 install.log.syslog
-rw-r--r-- 1 root root 482938 Jul 23 18:40 mysql.sql
Output-: (included –no-data)
[root@localhost ~]# mysqldump -u root -predhat --no-data mysql > mysqlnodata.sql
[root@localhost ~]# ll
total 568
-rw------- 1 root root 1137 Jul 15 16:19 anaconda-ks.cfg
drwxr-xr-x 2 root root 4096 Jul 16 10:16 Desktop
-rw-r--r-- 1 root root 29623 Jul 15 16:19 install.log
-rw-r--r-- 1 root root 3671 Jul 15 16:18 install.log.syslog
-rw-r--r-- 1 root root 23907 Jul 23 18:41 mysqlnodata.sql
-rw-r--r-- 1 root root 482938 Jul 23 18:40 mysql.sql
Taking backup only data-:
Syntax-: mysqldump –u root –predhat –no-create-info mysql > mysqlinfo.sql
By using the above command we can take the data backup. It wont include table structure. For verification see the below output.
Output-:
[root@localhost ~]# mysqldump -u root -predhat --no-create-info mysql > mysqlinfo.sql
[root@localhost ~]# ll
total 1028
-rw------- 1 root root 1137 Jul 15 16:19 anaconda-ks.cfg
drwxr-xr-x 2 root root 4096 Jul 16 10:16 Desktop
-rw-r--r-- 1 root root 29623 Jul 15 16:19 install.log
-rw-r--r-- 1 root root 3671 Jul 15 16:18 install.log.syslog
-rw-r--r-- 1 root root 460279 Jul 23 18:51 mysqlinfo.sql
-rw-r--r-- 1 root root 23907 Jul 23 18:41 mysqlnodata.sql
-rw-r--r-- 1 root root 482938 Jul 23 18:40 mysql.sql
Taking backup for all databases-:
To take the dump for all databases use the below syntax.
Syntax -: mysqldump –u username –ppassword –all-datbases > filename.sql
Restoring the database-:
Syntax-: mysqldump –u username –ppassword databasename < dumpfile.sql
By using the above command we can restore the database to the mysql. The database name should exist in the mysql.
Output-:
[root@localhost ~]# mysql -u root -predhat mysqlbkp < mysql.sql
[root@localhost ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqlbkp |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> \q;
Bye
I created the mysqlbkp database for restoring purpose(optional). Otherwise we can restore to the old database instead of creating new database.
Restoring all databases-:
To restore all the databases use the below command.
Syntax -: mysql –u username –ppassword < database.sql
Creating database-:
Syntax -: create database databasename;
Output -:
mysql> create database mysqlbkp;
Query OK, 1 row affected (0.00 sec)
To check the tables in a database -:
Syntax-: show full tables form databasename;
Before executing the command first we need to change into that database.
Step 1-: use databasename;
Step 2-: show full tables from databasename;
Output-:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| anji |
| mysql |
| test |
| wildfire |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mysql
Database changed
mysql> show full tables from mysql;
+---------------------------+------------+
| Tables_in_mysql | Table_type |
+---------------------------+------------+
| columns_priv | BASE TABLE |
| db | BASE TABLE |
| func | BASE TABLE |
| help_category | BASE TABLE |
| help_keyword | BASE TABLE |
| help_relation | BASE TABLE |
| help_topic | BASE TABLE |
| host | BASE TABLE |
| proc | BASE TABLE |
| procs_priv | BASE TABLE |
| tables_priv | BASE TABLE |
| time_zone | BASE TABLE |
| time_zone_leap_second | BASE TABLE |
| time_zone_name | BASE TABLE |
| time_zone_transition | BASE TABLE |
| time_zone_transition_type | BASE TABLE |
| user | BASE TABLE |
+---------------------------+------------+
17 rows in set (0.00 sec)
Note -: If sync is not happening properly between master and slave then use the below command. Before using the below command make sure that slave user is having all privileges. If the slave is configured as grant replication slave instead of grant all privileges then the below command wont work. For checking the privileges use the below command.
mysql > Show grants on username;
Syntax -:GRANT ALL PRIVILEGES ON *.* TO ‘USERNAME’@’%” IDENTIFIED BY ‘PASSWORD’
Syntax -: load data from master;
Or
Syntax-: stop slave;
Syntax-: start slave;
MySQL Replication -:
To Create mysql replication we need two servers. In that two servers we will make one as master and another as slave. For example assume that we have two servers..
Server 1-: 192.168.44.129 (Master)
Server 2-: 192.168.44.137 (Slave)
Master Server configuration file -:
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
log-slow-queries = /var/lib/mysql/mysql-slow.log
long_query_time = 2
log-error = mysql-error.log
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Disable Federated by default
skip-federated
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
slave-skip-errors = ALL
log-slave-updates
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
In the above configuration file observe the changes which are highlighted in bold text.
Slave server configuration file-:
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
wait_timeout = 60
connect_timeout = 10
group_concat_max_len = 32678
interactive_timeout = 120
join_buffer_size = 2M
key_buffer = 512M
max_allowed_packet = 64M
table_cache = 2048
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
skip-name-resolve
log-slow-queries = /var/lib/mysql/mysql-slow.log
long_query_time = 2
log-error = mysql-error.log
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Disable Federated by default
skip-federated
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
server-id = 2
master-host = 192.168.44.129
master-port = 3306
master-user = anjitesting
master-password = redhat
skip-slave-start
read-only
slave-skip-errors = ALL
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
In the above configuration file observe the changes which are highlighted in bold text. Don’t restart the server now.
After making the necessary changes in both the configuration files, need to create a mysql user for replication purpose in the master server.
Under Master-:
Step 1-:Mysql> GRANT REPLICATION SLAVE ON *.* TO ‘SLAVE_USER’ IDENTIFIED BY ‘<SOME PASSWORD>’;
Slave_user is the user name created for replication purpose. Give whatever name in the place of slave_user.
Step 2-: FLUSH PRIVILEGES;
Step 3-: show master status ;
Sample output-:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 592 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note down the bin-log file and position number.
Under slave-:
Before going to restarting the server check the changes made in configuration file. Then restart the server and check the slave status;
Mysql > slow slave status \G;
Sample output-:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.44.129
Master_User: anjitesting
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 592
Relay_Log_File: tiger2-relay-bin.000016
Relay_Log_Pos: 316
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 592
Relay_Log_Space: 316
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
ERROR:
No query specified
MySQL second instance-:
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3307
socket = /var/lib/mysql2/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3307
socket = /var/lib/mysql2/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
skip-name-resolve
log-slow-queries = /var/lib/mysql2/mysql-slow.log
long_query_time = 2
log-error = mysql-error.log
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
datadir = /var/lib/mysql2
# Disable Federated by default
skip-federated
# Replication Master Server (default)
# binary logging is required for replication
log-bin = mysql-bin
log-bin-index = mysql-log-bin.index
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 5
slave-skip-errors = ALL
log-slave-updates
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
After making necessary changes in the configuration file copy the mysql restart script for second instance.
#!/bin/sh
case "$1" in
start)
MYSTATUS=`/bin/netstat -an |grep 3307 |wc -l`
if [ $MYSTATUS == 1 ]
then
echo "MySQL 2nd Instance Already Running"
else
/usr/bin/mysqld_safe --defaults-file=/etc/my1.cnf & 2>&1 > /dev/null
echo "MySQL 2nd Instance Starting"
fi
;;
stop)
/usr/bin/mysqladmin --user=root --password='redhat' -S /var/lib/mysql2/mysql.sock shutdown 2>&1 > /dev/null
echo "MySQL 2nd Instance Stoping"
;;
*)
echo "Usage: `basename $0` {start|stop}"
;;
esac
After that restart the server. If it throws an error like [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist then try to execute below command.
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/
To set the password for second instance use the below command
mysqladmin -P 3307 -u root password 'redhat' -S /var/lib/mysql2/mysql.sock
