MYSQL Configuration

INTRODUCTION

MYSQL is an open source relational database management system.

MySQL is offered under two different editions:

 1) Open source MySQL Community Server 

 2) Proprietary Enterprise server.

Some of the major features available in Mysql

1) Cross platform support

2) Stored procedures

3) Triggers

4) Cursors

5) Information schema

6) Multi-master replication in Mysql clustring

7) Storage Engines: InnoDB, MyISAM, Archive, Blacklhole, merge, memory heap. 

Installing MySql

Follow bellow steps

~]# yum install mariadb-server mariadb
~]# mysql_secure_installation

After running above command you will get prompt for some details as shown in bellow screen:

                                                

Creating Mysql Database and Table as shown in bellow screen:

Taking MySql Database Backup and Restoration

1) Taking backup of mysql database.

~]# mysqldump -u [username] -p[password] databasename > backupfilename
~]# mysqldump -u root -ppassword testdata > testdata.sql

2) Taking backup of all databases.

~]# mysqldump -u root -ppassword --all-databases > alldata.sql

3) Restoring Mysql database.

~]# mysqldump -u root -ppassword databasename < dumpfile

Setting MySql Master-slave Replication

In master server open configuration file my.cnf and add bellow lines:

~]# vi /etc/my.cnf
server-id=1                                                                                                                                                             binlog-do-db=databasename                                                                                                                                              relay-log= /var/lib/mysql/mysql-relay-bin                                                                                                                          relay-log-index= /var/lib/mysql/mysql-relay-bin.index                                                                                                      log-error= /var/lib/mysql/mysql.err                                                                                                                                        master-info-file= /var/lib/mysql/mysql-master.info                                                                                                           relay-log-info-file= /var/lib/mysql/mysql-relay-log.info                                                                                                 log-bin= /var/lib/mysql/mysql-bin                                                                                                                                ~]# systemctl restart mariadb                                                                                                                                               ~]# mysql -u root -p                                                                                                                                                                                    mysql> GRANT REPLICATION SLAVE ON *.* TO 'USER'@'%' IDENTIFIED BY 'PASSWORD';                                                              mysql> FLUSH PRIVILEGES;                                                                                  

In slave server open configuration file my.cnf and add bellow lines:

~]# vi /etc/my.cnf
server-id=2                                                                                                                                                       master-host=master_server_ip                                                                                                                                                   master-connect-retry=60                                                                                                                                                       master-user=user                                                                                                                                                                          master-password=password                                                                                                                                                                   replicate-do-db=databasename                                                                                                                                                          relay-log= /var/lib/mysql/mysql-relay-bin                                                                                                                             relay-log-index= /var/lib/mysql/mysql-relay-bin.index                                                                                                  log-error= /var/lib/mysql/mysql.err                                                                                                                                  master-info-file= /var/lib/mysql/mysql-master.info                                                                                                        relay-log-info-file= /var/lib/mysql/mysql-relay-log.info                                                                                                   log-bin= /var/lib/mysql/mysql-bin                                                                                                                              ~]# systemctl restart mariadb                                                                                                                                            ~]# mysql -u root -p                                                                                                                                                                                mysql> show slave status\G                                                                                 

Verify replication by inserting data in master server whether that data is replicating in slave server or not.

Note: If you already having data present in master server then you can take a backup of database from master server by using above backup command and restore that database in slave server by locking tables or stopping mysql service on master server to update data on slave server.