Maria DB Replication - Master Slave.

Maria DB Replication.

Configuring the master

1. Enable binary logging if it's not already enabled.
2. Give the master a unique server_id. All slaves will also be given a server_id. This can be a number from 1 to 2³²-1, and must be unique for each server in the replicating group.
3.  Slaves will need permission to connect and start replicating from a server. Usually this is done by creating a dedicated slave user, and granting that user permission only to replicate (REPLICATION SLAVE permission).

Example

In your my.cnf file.

log-basename=master

log-bin=/var/log/mariadb/mariadb-bin

binlog-format=row

server_id=1

 Execute in the SQL prompt:

GRANT REPLICATION SLAVE ON *.* TO replication_user;

Variables to check                                           

There are a number of options that may impact or break replication. Check the following variable settings to avoid problems.

·         Skip-networking. If skip-networking=1, the server will limit connections to localhost only, and prevent all remote slaves from connecting.

·         bind_address. Similarly, if the address the server listens for TCP/IP connections is 127.0.0.1 (localhost), remote slaves connections will fail.

Configuring the slave                             

        Give the slave a unique server_id. All servers, whether masters or slaves, are given a server_id. This can be a number from 1 to 2³²-1, and must be unique for each server in the replicating group. The server will need to be restarted in order for a change in this option to take effect.

Getting the master's binary log co-ordinates

1. Now you need prevent any changes to the data while you view the binary log position. You'll use this to tell the slave at exactly which point it should start replicating from.

On the master, flush and lock all tables by running.           

FLUSH TABLES WITH READ LOCK;

Keeps this session running - exiting it will release the lock.

      2.  Get the current position in the binary log by running SHOW MASTER STATUS; 

SHOW MASTER STATUS;

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

| File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mariadb-bin.000096 |      568 |                       |                           |

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

Record the File and Position details. If binary logging has just been enabled, these will be blank.

·         Now, with the lock still in place, copy the data from the master to the slave.

·         Once the data has been copied, you can release the lock on the master by running UNLOCK TABLES.

                                                  UNLOCK TABLES;

 Start the Slave

      Once the data has been imported, you are ready to start replicating. Begin by running a CHANGE MASTER TO, making sure that MASTER_LOG_FILE matches the file and MASTER_LOG_POS the position returned by the earlier SHOW MASTER STATUS.

Ex:

CHANGE MASTER TO

  MASTER_HOST='192.168.1.110',

  MASTER_USER='replication_user',

  MASTER_PASSWORD='Coffee$123#',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='mariadb-bin.020496',

  MASTER_LOG_POS=4987,

  MASTER_CONNECT_RETRY=10;

        Now start the slave with the START SLAVE command.

   START SLAVE;

Upgrading MySQL Server.

Follow the below steps to upgrade MySQL Server to a newer version.

1. First take the backup of all databases in the present MySQL Server.
2. Remove the MySQL Server Installed in the server.
3. Download the new RPM/Binary Packages from http://www.mysql.com/downloads
4. Install the packages in the Server.
5. Give the permissions to the server i.e., /var/lib/mysql folder.
6. Run mysql_upgrade
7. Now Restore the databases on the server.
8. Now check the server whether all the database tables have been on the server.

Installing MySQL Server through Binaries in Linux.

To install and use a MySQL binary distribution, the command sequence looks like this:

shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /data
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &

Create a mysql User and Group.
First we are going to create a group of mysql in the new server, adding a new user of mysql to server so that we will connect to the mysql by using the above username.

shell> groupadd mysql

shell> useradd -r -g mysql mysql

Obtain and Unpack the Distribution

Select directory under which you want to unpack the distribution and change location into it. The example here unpacks the distribution under /data. The instructions, therefore, assume that you have permission to create files and directories in /data. If that directory is protected, you must perform the installation as root.

shell> cd /data

Unpack the distribution, which creates the installation directory. Then create a symbolic link to that directory.tar can uncompress and unpack the distribution if it has z option.

shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz

shell> ln -s full-path-to-mysql-VERSION-OS mysql

Provide the Right to the Folder:
After unpacking the data to the folder. Now we have to give the permissions to the folder.

shell> chown -R mysql .

shell> chgrp -R mysql .

Installing MySQL DB.

We are going to install the MySQL DB i.e. by the below command. so the database and relevant contents will be created in the folder.

shell> scripts/mysql_install_db --user=mysql

Now give the mysql user rights to the folder so that we can access the MySQL Server. And it can be used for creating the databases on the server.

Data Directory Files in MySQL.

The MySQL Server consists of below files...

1. mysql
2. peformance_schema
3. test
4. iblogfile0
5. iblogfile1
6. ibdata1

mysql

This database consists of tables which are related to mysql's users, time which are associated to mysql installed on the server.

test

This is a test database. This can be deleted if the DBA doesn't want.

iblogfile0 & iblogfile1

These are the log files on the Mysql Server Data directory. when ever any user enter the data / any operations that are performed on the innodb_buffer_pool & from there it comes to iblogfile0 & iblogfile1. After some checkpoints the data is committed to the disk.

ibdata1

ibdata1 is the table space data file and its log files.

If innodb is not specified then mysql creates an auto_extending data file slightly longer than 100MB

Moving DATA DIR From One Location to Another.

Follow the below steps.

1. sudo /etc/init.d/mysqld stop

2. Copy the existing data directory (/var/lib/mysql) to a new path (/data/mysql).

sudo cp -R -p /var/lib/mysql  /data/mysql

3. gedit /etc/my.cnf

Change the data directory path in my.cnf to the new data dir / socket file path.

4. Give the permissions.

Chown -R mysql:mysql /data/mysql (New path)

5. Rename the old Directory.

mv /var/lib/mysql  /var/lib/mysql-old

6. Create a symbolic link.

ln -s /data/mysql /var/lib/mysql

 

MyIsam Vs InnoDB

MyISAM	Innodb
Not *ACID compliant and non-transactional	*ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys
MySQL 5.0 Default Engine	Default Engine in MySQL 5.5
Offers Compression	Offers Compression
Requires full repair/rebuild of indexes/tables	Auto recovery from crash via replay of logs
Changed Db pages written to disk instantly	Dirty pages converted from random to sequential before commit and flush to disk
No ordering in storage of data	Row data stored in pages in PK order
Table level locking	Row level locking

Restore a Single Database from a list of 10 Databases.

Please follow the below steps to restore a single database from a Full Servers Mysqldump backup.

1. First take the backup of all databases using the mysqldump.

mysqldump -u root -p --all-databases > /data/dbbackup.sql

2. Now drop a single Database name monkey.
Drop database monkey;

3. Connect to MySQL. Create a database with the name that was deleted in mysql.
Create Database monkey;

4. Now restore the database with whole dump.

mysql -u root -p monkey < /data/dbbackup.sql

5. Now the database name monkey will be restored. And we can check the data present in all the tables.

Installing MySQL Server using RPM.

Installing MySQL Server using RPM.

MySQL can be installed on the Linux distributions by using below methods.

1. RPM

2. MySQL Binaries Installation

3. YUM Repository.

1. Download the latest stable release of MySQL.

MySQL can be downloaded from http://dev.mysql.com/downloads/mysql/. Please select the appropriate RPM used for installation on Linux server. Please select the RPM's according to the installation i.e., 32 bit or 64 bit.

Please download the below RPM from the above site after selecting the Red Hat Linux.

1. MySQL-shared-compat-5.6.23-1.el7.x86_64.rpm

2. MySQL-test-5.6.23-1.el7.x86_64.rpm

3. MySQL-devel-5.6.23-1.el7.x86_64.rpm

4. MySQL-client-5.6.23-1.el7.x86_64.rpm

5. MySQL-server-5.6.23-1.el7.x86_64.rpm

6. MySQL-shared-5.6.23-1.el7.x86_64.rpm

7. MySQL-embedded-5.6.23-1.el7.x86_64.rpm

2. Remove the existing default MySQL that came with the Linux distro.

# rpm -qa | grep -i mysql

mysql-5.0.22-2.1.0.1

mysqlclient10-3.23.58-4.RHEL4.1

3. Install the downloaded MySQL package

Install the MySQL Server and Client packages as shown below.

# rpm -ivh MySQL-server-5.6.23-1.el7.x86_64.rpm

# rpm -ivh MySQL-client-5.6.23-1.el7.x86_64.rpm

Install all the RPM's of MySQL Server one by one. After the installation the mysqld process will be started. Then we can connect to the mysql server.

4. Perform post-install security activities on MySQL.

We should set a password for the root user as shown below:

# /usr/bin/mysqladmin -u root password 'Coffee$1234#'

The best option is to run the mysql_secure_installation script that will take care of all the typical security related items on the MySQL as shown below. On a high level this does the following items:

1. Change the root password

2. Remove the anonymous user

3. Disallow root login from remote machines

4. Remove the default sample test database

Please run the below command on the server

# /usr/bin/mysql_secure_installation

After running the command it ask user to change the root password, removing any anonymous users on the server, and the server do not allow the root logins from remote machines and removing the default sample databases on the server.

5. Verify the MySQL installation:

# mysql -V

The above command show the information of the mysql version installed on the Linux machine.

Connect to the MySQL database using the root user and make sure the connection is successfull.

# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 13

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Follows the steps below to stop and start MySQL

# service mysql status

MySQL running (12588) [ OK ]

# service mysql stop

Shutting down MySQL. [ OK ]

# service mysql start

Starting MySQL. [ OK ]

MySQL Backup and Restore of a Database.

MySQL Database is used by many organisations for the Production, Development and Testing of the servers. It is very important to take the backup of data regularly.

1. Back up From the Command Line (using mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

• [uname] Your database username
• [pass] The password for your database (note there is no space between -p and the password)
• [dbname] The name of your database
• [backupfile.sql] The filename for your database backup
• [--opt] The mysqldump option

For example, to backup a database named 'Manish' with the username 'root' and with no password to a file manish_backup.sql, you should accomplish this command:

$ mysqldump -u root -p Manish > manish_backup.sql

This command will backup the 'Tutorials' database into a file called manish_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only manish_a and manish_b tables from the 'Manish' database accomplish the command below. Each table name has to be separated by space.

$ mysqldump -u root -p Manish Manish_a Manish_b > Manish_backup.sql

Sometimes it is necessary to back up more that one database at once. In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

$ mysqldump -u root -p --databases Manish Articles Comments > content_backup.sql

If you want to back up all the databases in the server atone time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

$ mysqldump -u root -p --all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

--add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

--no-data: Dumps only the database structure, not the contents.

--add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

Advantages of using mysqldump: It is simple to use and it takes care of table locking issues for you.

Disadvantages:Is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

2. Restoring your MySQL Database

Above we backup the Tutorials database into manish_backup.sql file. To re-create the Tutorials database you should follow two steps:

• Create an appropriately named database on the target machine
• Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Manish < manish_backup.sql

To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

How to take the dump of Stored procedures, Functions & Triggers.

We can take the backup of the Stored Procedures, Functions and Triggers as below.

# mysqldump --routines > outputfile.sql

#mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt datbasename > /backups/db_bakup.sql

# mysql databasename < /backups/db_bakup.sql

 

MySQL Commands.

The below commands are useful for connecting to the MySQL.

1. How to check the MySQL Status.

# service mysqld status

2. Connecting to the MySQL.

# mysql -u root -p

Here we have to provide the password whenever it prompts for it. After that we will be connecting to the server.

3. How to START / STOP a Service.

# service mysqld start

# service mysqld stop

# service mysqld restart

4. To see the list of databases.

# show databases;

5. How to switch from one database to another database.

# USE <dbname>;

# USE <Manish>;

6. To see the list of tables.

# Show Tables;

7. To Delete a table.

# Drop table <tablename>;

# Drop table manish_a;

8. To Drop a Database.

# Drop Database <dbname>

# Drop Database Manish;

9. To see the MySQL Version;

# SELECT VERSION();

10. To see the current Date.

# SELECT CURRENT_DATE();

11. To see the date and Time;

# SELECT NOW();

12. To see the user.

# SELECT USER();