Category Archives: MySQL

MySQL Replication Setup

Mysql uses a Master-slave/Publisher-Subscriber model for Replication. MySQL replication is an asynchronous replication. In MySQL replication master keeps a log of all of the updates performed on the database. Then, one or more slaves connect to the Master(Publisher Server), read each log entry, and perform the indicated update on the slave (Subscriber) server databases. The master server is responsible for the track of log rotation and access control.

Each slave server has to keep the track of current position within the server’s transaction log. As new transactions occur on the server, they get logged on the master server and downloaded by each slave. Once the transaction has been committed by each slave, the slaves update their position in the server’s transaction log and wait for the next transaction.

In this article, I will show you the steps to configure the Master/Slave replication between two servers.

Step 1: Create a user on Master server which Slave server can use to connect. I have created the user named “repl_user”.

--Connect to MySQL Master server
mysql -u root -proot
--Execute the below code
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Step 2: We have to change the MySQL configuration file usually in the /etc/mysql.cnf location. Here we will add the replication configuration parameters.

log-bin – will be used to write a log on the desired location
binlog-do-db – will be used to enabled the database for writing log. I have used Publisher_Database, you have to specify your database name.
server-id – Specify the ID of the Master server

log-bin = /home/mysql/logs/mysql-bin.log
binlog-do-db=publisher_database
server-id=1

Step 3: Once you have added the above configuration parameters into the My.cnf, next step is restart the MySQL Master Instance.
You can use below command to restart the MySQl service.

/etc/init.d/mysqld restart
service mysqld restart

Step 4: We have to configure the /etc/my.cnf file on the slave server. Here we will add the below parameters in the configuration file.

server-id – gives the Slave its unique ID
master-host – tells the Slave the I.P address of the Master server for connection. You can get the IP address using IPConfig command.
master-connect-retry – Here we will specify the connection retry interval.
master-user – Specify the user which has permission access the Master server
master-password – Specify the password of the replication user mentioned above
replicate-do-db – Specify the subscriber database name
relay-log – direct slave to use relay log

server-id=2
master-host=128.20.30.1
master-connect-retry=60
master-user=repl_user
master-password=password
replicate-do-db=subscriber_slave
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

Step 5: Restart the slave MySQl instance

/etc/init.d/mysqld restart
service mysqld restart

Step 6: If your Master MySQL instance is live instance, you have to do the backup/restore using MySQLDump utility.

--Connect to MySQL Master server
mysql -u root -proot

--Stop the write operation
FLUSH TABLES WITH READ LOCK;

--Generate the dump of the database (backup)
--gzip command will compress the file and create the zip file name backup.sql.gz
mysqldump publisher_master -u root -p > /home/my_home_dir/backup.sql;
gzip /home/my_home_dir/backup.sql;

--execute below copy command on slave to copy the backup file
scp root@128.20.30.1:/home/my_home_dir/database.sql.gz /home/my_home_dir/

--Once copied, extract the file using gunzip
gunzip /home/my_home_dir/backup.sql.gz

--restore the databsae
mysql -u root -p subscriber_slave  </home/my_home_dir/backup.sql

Step 7: Execute the SHOW MASTER STATUS command on Master server. It will give you the bin log file name and position which we will use specify the slave.

SHOW MASTER STATUS;


+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 707 | exampledb | |
+------------------+----------+--------------+------------------+

Step 8: Execute the below commands on slave.

--Connect to MySQL Slave server
mysql -u root -proot
--stop the slave
slave stop;
-- Execute the below command
CHANGE MASTER TO MASTER_HOST='128.20.30.1', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=707;
--Start the Slave
slave start;

Step 9: Login to Master MySQL instance and unlock the tables.

--Connect to MySQL Master server
mysql -u root -proot
-- unlock the tables if you have executed lock tables command
unlock tables;

You are all set. Master to Slave replication has been started. Make sure while configuring the my.cnf file.
1. Take the copy of my.cnf file before starting the replication configuration.
2. Make sure skip-networking parameter is not enabled in the my.cnf file.

MySQL Replication

Problem/Error

Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave

Resolution Steps

You have to follow below steps to troubleshoot the error.

Execute the below command

SHOW MASTER STATUS

SHOW SLAVE STATUS

Check the error log for replication and its position.

Ideally there are three sets of file/position coordinates in SHOW SLAVE STATUS to identify the correct file

1) The position, ON THE MASTER, from which the I/O thread is reading: Master_Log_File/Read_Master_Log_Pos.

2) The position, IN THE RELAY LOGS, at which the SQL thread is executing: Relay_Log_File/Relay_Log_Pos

3) The position, ON THE MASTER, at which the SQL thread is executing: Relay_Master_Log_File/Exec_Master_Log_Pos

Next you have to check the error log for the log position to identify the correct binary log file and set the correct log file using below command.

CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000480′

Once the problem is resolved you can use Maatkit tool to sync table to multiple slaves.

mk-table-checksum command is used to check what tables are out of sync and when use mk-table-sync command is used to resync them.

Memcached & MySQL

memcached (pronunciation: mem-cash-dee.) is a general-purpose distributed memory caching system that was originally developed by Danga Interactive for LiveJournal, but is now used by many other sites. It is often used to speed up dynamic database-driven websites by caching data and objects in memory to reduce the number of times an external data source (such as a database or API) must be read. Memcached is distributed under a permissive free software license. Memcached lacks authentication and security features, meaning it should only be used on servers with a firewall set up appropriately. By default, memcached uses the port 11211. Among other technologies, it uses libevent. Memcached’s APIs provides a giant hash table distributed across multiple machines. When the table is full, subsequent inserts cause older data to be purged in least recently used (LRU) order. Applications using memcached typically layer memcached requests and additions into core before falling back on a slower backing store, such as a database. You can download memcached API from http://www.danga.com/memcached/

 

How to recover root password in MySQL?

  1. As Linux system root user stop the database process: /etc/init.d/mysql stop
    (or: service mysql stop)
  2. Start MySQL in safe mode and skip the use of the “grant tables”: /usr/bin/mysqld_safe –user=mysql –socket=/var/lib/mysql/mysql.sock –pid-file=/var/run/mysqld/mysqld.pid –datadir=/var/lib/mysql –skip-grant-tables –skip-networking &
  3. Reset the MySQL root password: mysqladmin -u root flush-privileges password newpassword
  4. Stop MySQL running in safe mode: kill `cat /var/run/mysqld/mysqld.pid`
  5. Start MySQL: /etc/init.d/mysql start
  6. The new MySQL root password can now be used: mysql -u root -p
    Respond withthe password: newpassword

MySQL and Linux commands

Where are the MySQL binaries stored? Find the location? : which mysql

 

Find the version of MySQL: mysql -V

 

Start the MySQL server: /usr/bin/mysqld_safe –user=mysql & or /etc/init.d/mysql start

 

Validate MySQL process has started: ps -afe | grep mysqld

 

Validate MySQL server is up and running: mysql> SHOW VARIABLES LIKE “%version%”;

 

Find the columns of the table in MySQL: mysql> SHOW COLUMNS FROM dept;

 

Find the table related information: SHOW TABLE STATUS LIKE ‘dept’ \G

 

Display all tables: Mysql> show tables

 

Display all databases: Myql> show databases

 

Display tables from the non current database: Mysql> SHOW TABLES FROM database_name;

 

List the filed of the tables

mySQL> describe tableName

 

Backup/Dump all databases

[mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

 

Backup/Dump particular database

[mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

 

Backup/Dump a table from a database

[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

 

Restore database (or database table) from backup

[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

 

 

 

 

 

How to decide Primary Key column data type in MySQL? Expectation with the Primary Key column

Decide the Primary Key column is one of the important tasks. Typically, the Primary Key is must mitigate the below properties.

 

(1) Uniquely identify each row in a table

(2) Can be referenced by another column/row (foreign key)

(3) provide a “natural” sequencing of the data (alphabetical, numeric, and chronological)

(4) Partition the data horizontally for performance

 

If your interest is either “unique identifier” or “natural sequencing”, you will be using whatever data type fits for the rows natural identifier. So that pretty much answers the question, and should be your first choice.

 

Remember that a primary key must be immutable — never changes — so using a natural key may not be possible. In that case, a surrogate key becomes a necessity.

 

A surrogate key will probably break your “natural sequencing”, since the data will be indexed on the surrogate key value, not on the natural values. This doesn’t mean to avoid it, but only to recognize that condition and address it in your design (an index, for example, restores the sequencing but adds overhead to update/delete/insert operations).

 

Also, if you are looking at this as a performance problem, you may want to substitute an integer data type as a “surrogate key” for the natural primary key.

 

If you use a surrogate key, use an integer that is sized to the native size of the platform (32-bit, 64-bit, 128-bit integer) up to the size needed to support uniqueness.

 

I would avoid integers which increment monotonically (by 1) — this presents a “bottleneck” in the processing, making it complicated to run multiple, parallel, concurrent insertions. If you’re running an OLTP system, that bottleneck will limit your performance and scalability.

 

So ultimately to decide the primary key data type we need to project the data growth, number of insertion, number of selection and performance issues.

 

For the normal table you can use primary key datatype as BIG INT with AUTO_INCREMENT option.