MySQL 5.6 Replication with GTID – Global Transaction ID

Hi guys, Early February Oracle released the new version of MySQL named 5.6, one of the enhancements is the GTID (Global Transaction ID)

GTID is an unique identifier which will be added at each transaction, and will be very useful on the slave. remember before we needed to set MASTER_LOG_FILE and MASTER_LOG_POS, now we don’t need them anymore.

Let’s see some new variables which we need to add to our cnf file:
gtid-mode : It will enable GTID, in order to this function work, we need to turn on log-bin and log-slave-updates
enforce-gtid-consistency : It will guarantee that only allowed command will be executed ( more information here)

Basicly, is only this what we need to enable GTID, for this tutorial I will use 2 virtual machines, black (master – 192.168.1.110) and white (slave – 192.168.1.111), see bellow my cnf file:

black.cnf

[mysqld]
...
binlog-format=MIXED
log-bin
log-slave-updates=true
gtid-mode=on 
enforce-gtid-consistency=true 
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
master-verify-checksum=1
server-id=1
report-host=black

white.cnf

[mysqld]
...
binlog-format=MIXED
log-slave-updates=true
log-bin
gtid-mode=on 
enforce-gtid-consistency=true 
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=2
report-host=white

Next steep, we need to create the replication user on master (black):

GRANT REPLICATION SLAVE ON *.* TO replication@192.168.1.111 IDENTIFIED BY 'reppwd';
FLUSH PRIVILEGES;

In case you serve already has data, you can follow this post to create a backup from your master, note that now when we raw the SHOW MASTER STATUS; we see a new field named Executed_Gtid_Set, and then restore the backup on slave

Let’s start the replication, the very good stuff in here is that we don’t need to worry about the binary log and position anymore, is just simple as that with GTID:

CHANGE MASTER TO MASTER_HOST='192.168.1.110', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

At this point, you should have your replication running with GTID, lets see :

white>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.110
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: black-bin.000002
          Read_Master_Log_Pos: 936
               Relay_Log_File: white-relay-bin.000003
                Relay_Log_Pos: 1146
        Relay_Master_Log_File: black-bin.000002
             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: 936
              Relay_Log_Space: 1560
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2671c08b-7cf0-11e2-ac39-00163ebee7c2
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2671c08b-7cf0-11e2-ac39-00163ebee7c2:1-4
            Executed_Gtid_Set: 2671c08b-7cf0-11e2-ac39-00163ebee7c2:1-4
                Auto_Position: 1
1 row in set (0.00 sec)

That Retrieved_Gtid_Set and Executed_Gtid_Set displays the transaction that we read from master, and the transaction that we already executed.

Let’s check if the commands executed on master are replied on slave:

black> CREATE DATABASE gtid_test;
Query OK, 1 row affected (0.00 sec)

black> use gtid_test;
Database changed
black> CREATE TABLE test (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

black> INSERT INTO test (id) VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

black> 

Now let’s check it on slave:

white> USE gtid_test;
Database changed
white> SELECT * FROM test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

white>

Done, we have our replication running with Global Transaction ID (GTID)

Liked? Help and Share!
Posted in backup, binlog, install, replication | 2 Comments

Consistent backup – snapshot

Hi guys, today let’s learn about how to have a consistent backup (snapshot)
First of all, in what situations do we use a snapshot?

1. Lets say that your production server now will have a replica, how do you do the first load of data in this slave? what was the master bin log position when you started the backup, during the backup process, does anyone wrote any query to db?

2. In case you want to implement an incremental backup strategy, you can take a snapshot once a week and in case you need to restore you server, you just restore the snapshot and apply the binary logs.

Then, let’s start.

To grantee this data integrity we will need 2 sessions open on master, first one to lock all databases, second one to do the copy

Then, let’s go:

Session 1:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

You will receive an output like this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

IMPORTANT: LEAVE THIS SESSION OPEN DURING ALL PROCESS, IF YOU CLOSE THIS SESSION, THE DATABASE LOCK WILL BE RELEASED

To do the backup, we will use mysqldump:
Session 2:

mysqldump -u usuario -p --all-databases > all_dbs.sql

After the backup is completed, make sure it ends with:
— Dump completed on xxxx-xx-xx xx:xx:xx

Done, we now ca release the lock on session 1:

UNLOCK TABLES;

Is this, we have a snapshot of our database.

Liked? Help and Share!
Posted in backup | 2 Comments

MySQL Replication with SSL

Hi folks, let’s continue talk about replication, you can see the basics in this other post

First of all, lets create the ssl certificates, go to the master server:
Create CA certificate:

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Create server certificate:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Create client certificate:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

copy ca-cert.pem, client-req.pem, client-cert.pem to slave server

Adjust the cnf file:

ssl
ssl-ca=/etc/mysql/sslcerts/ca-cert.pem
ssl-cert=/etc/mysql/sslcerts/server-cert.pem
ssl-key=/etc/mysql/sslcerts/server-key.pem

Make sure you restart the MySQL service, and check if the ssl is enabled

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/sslcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/sslcerts/server-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/sslcerts/server-KEY.pem  |
+---------------+-------------------------------------+
7 rows IN SET (0.00 sec)

Then, we will need a replication user, and we will specify that this user require ssl:

GRANT REPLICATION SLAVE ON *.* TO 'user'@'178.77.90.38' IDENTIFIED BY 'PASSWORD' REQUIRE SSL;
FLUSH PRIVILEGES;

Ok, we have finish your work on master, let’s work on slave server:

Adjust the cnf file:

ssl
ssl-ca=/etc/mysql/sslcerts/ca-cert.pem
ssl-cert=/etc/mysql/sslcerts/client-cert.pem
ssl-key=/etc/mysql/sslcerts/client-key.pem

Let’s check if is everythink ok with ssl

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/sslcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/sslcerts/client-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/sslcerts/client-KEY.pem  |
+---------------+-------------------------------------+
7 rows IN SET (0.00 sec)

Finally, Start the replica:

CHANGE MASTER TO MASTER_HOST='xxxxxx', MASTER_USER='user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/sslcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/sslcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/sslcerts/client-key.pem';
START SLAVE;
Liked? Help and Share!
Posted in MySQL, password, replication, Security | 2 Comments

How to rename table in MYSQL

To rename a table in MySQL you just need to run a command named RENAME TABLE, the syntax is very easy to use,

RENAME TABLE tb1 TO tb2;

The RENAME TABLE command will rename the table atomically, which means your table will be locked during the command.
You can also rename more than one table in one command:

RENAME TABLE tb1 TO tb2, tb3 TO tb4;

To rename the table you will need ALTER and DROP privileges on the old table and CREATE and INSERT on the new one.

You just need to be careful if you use triggers, on the bellow example, after a INSERT on table Cities, I count +1 to NOfCities on the iserted Countries, what happens if I rename the table Contries?

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: PlusOneCities
               Event: INSERT
               Table: Cities
           Statement: UPDATE Countries SET NOfCities = NOfCities + 1 WHERE Code = NEW.CountryCode
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> INSERT INTO Cities SET Name = 'City1', CountryCode = 'BRA', District = 'Brasilia', Population = 10000;
Query OK, 1 row affected (0.04 sec)

mysql> RENAME TABLE Countries TO Country;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Cities SET Name = 'City2', CountryCode = 'BRA', District = 'Brasilia', Population = 10000;
ERROR 1146 (42S02): Table 'world.Countries' doesn't exist

See, we need to manualy change all triggers.

You can find more information on MySQL Documentation

Liked? Help and Share!
Posted in Uncategorized | 4 Comments

MySQL IN AND OR

MySQL IN??? MySQL OR??? MySQL AND???
When I use one and when I use other?

we will use the city table to this tutorial:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+



Let’s see a simple rule to clarify this:

AND – we use AND to add a new condition which will need to be true:

SELECT * FROM City WHERE CountryCode = 'BRA' AND Population > 1000000 AND District LIKE 'São Paulo';

This query will return Cities which CountryCode is BRA and Population is bigger than 1000000 and Discrict is São Paulo, this three condition need to be true.

OR – we use OR to add a new condition which or one or other needs to be true:

SELECT * FROM City WHERE Population > 1000000 OR District LIKE 'São Paulo';

This query will return Cities which have a population bigger than 1000000 or have São Paulo as their district .
IN – we use in the same way which we use OR but for the same column:

SELECT * FROM City WHERE CountryCode = 'USA' OR CountryCode = 'BRA' OR CountryCode = 'IRL';
SELECT * FROM City WHERE CountryCode IN ('USA', 'BRA', 'IRL');

This both queries will return the same result, all cities which are in USA, BRA or IRL.

Liked? Help and Share!
Posted in Uncategorized | 2 Comments

Recording session on MySQL

Hi guys, today let’s learn about a very useful option on mysql command line to recording all what happen in your mysql session, It’s the option –tee. Is very easy to use, you just need to add the –tee=/path/to/file

mysql -u root -p --tee=/tmp/log.mysql

It is all for today!
Enjoy!!!

Liked? Help and Share!
Posted in Uncategorized | Leave a comment

Date format in MySQL

Hi guys, today let’s talk about how to format date in MySQL
For this tutorial I’m using mysql server 5.5
Basically you need to know how to use a function called DATE_FORMAT
Let’s create a table and insert some rows:

CREATE TABLE IF NOT EXISTS `dates` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `dates` (`id`, `date`) VALUES
(1, '2012-10-17 13:39:55'),
(2, '2012-10-09 09:23:11'),
(3, '2012-08-24 16:47:07');

OK? then let’s select this values and show it formated

SELECT id, DATE_FORMAT( `date` , '%d/%c/%Y %H:%i:%s' ) AS `date` FROM `dates`

It’s easy, just remember which the format is always %ONE_LETTER, where this letter is the field which you want to show (see the complete documentation)

Liked? Help and Share!
Posted in MySQL | 3 Comments

How to change user password on MySQL

Today let’s talk about how to change MySQL user password

We can use 2 ways, 1 – mysqladmin, 2 – linguagem SQL

This is a step by step video of this post:

1. mysqladmin:

The syntax is easy:

 mysqladmin -u USER -p password NEWPASSWORD 

Let’s then change the password of ‘marcelo’ user to ‘123’

mysqladmin -u marcelo -p password '123'

For this command, we have 3 problems:

. You can just change your own user

. You need SUPER PRIVILEGES to run this command

. If you share you linux user account with other users, this command will appear on historic, to avoid it we can edit ~/.bash_history and delete this lines

2. SQL (the best on my opinion):

To change the password, we’ll just run an update on user’s table on mysql db, you can do this in 2 ways, both have the same result

SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass');

ou

UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='user' AND Host='host'; 

After run the above command, we need to FLUSH PRIVILEGES to tell mysql to reload the user table

Let’s change the password of ‘marcelo’ user to ‘123’

UPDATE mysql.user SET Password=PASSWORD('123') WHERE User='marcelo' AND Host='localhost';

FLUSH PRIVILEGES;

If you are using mysql command line, you also will have the same problem as mysqladmin, the historic , to hide it, we can edit the file on ~/.mysql_history

Is this, I hope you enjoy!
Any question, just leave a comment

Liked? Help and Share!
Posted in MySQL, password | 8 Comments

Using the Mysql FullText Index Search

Today let’s talk about a resource very useful on MySQL, the FullText Index and Search
This resource is very powerful, today on versions 5.5 is just available to MyISAM engine, but, like we can see on MySQL FullText documentation, it will be available also to InnoDB on MySQL 5.6

Usually when we want to search for a word or expression, we use LIKE ‘%word%’, in case we are looking for more than one word we use LIKE ‘%word1%word2%’, what many people don’t know is for this kind of search is expensive and not optimized to our MySQL, in this cases we solve our problems with FullText Index
the syntax is easy, MATHC() … AGAINST (), where MATCH we specified the name(s) of column(s) which we are looking for, yes, we can look for more then one column, we just need all this columns specified on our index and AGAINST is where we specify the word(s) which we are looking for, we can also, specified a search mode, but I will talk about it later

Let’s create our table and start it

CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`body` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM;
INSERT INTO `articles` VALUES (1,'MySQL Tutorial','DBMS stands for DataBase ...'),(2,'How To Use MySQL Well','After you went through a ...'),(3,'Optimizing MySQL','In this tutorial we will show ...'),(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),(5,'MySQL vs. YourSQL','In the following database comparison ...'),(6,'MySQL Security','When configured properly, MySQL ...');

Let’s do our first query, looking for articles which approach ‘database’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.01 sec)

Now, let’s look for ‘database tutorial’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('database tutorial');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
3 rows in set (0.01 sec)

Now, let’s search for articles which approach ‘MySQL’, just a detail, all of our articles contain the word ‘MySQL’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('MySQL');

Empty set (0.00 sec)

Why? Let’s talk about Search Modes, by default, MySQL uses Natural Language mode, which tell us if the searched word match if 50% or more rows, the entire query doesn’t match.
Other good function with MySQL allow us to use, is change our search mode, let’s use Boolean mode:

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('MySQL' IN BOOLEAN MODE);

+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

6 rows in set (0.00 sec)

Ok, but what is the difference between they? why use boolean mode? like says on name, is true or false, let’s do a query looking for ‘database’ but I don’t want to show rows which contain the word ‘tutorial’, is this difficult? no, let’s see:

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('+database -tutorial' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
1 row in set (0.01 sec)

Is this for today, on next article, we will learn more about the boolean mode, changing the order of relevance

Liked? Help and Share!
Posted in index, MySQL, performance | 30 Comments

How to rename a database in MySQL?

Today we will talk how do you can easily rename a database in MySQL.
At the MySQL version 5.1.7 was added a command to do this job named RENAME DATABASE

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

But it was removed on the MySQL version 5.1.23 because maybe it would result in data lost’s like you can see on this link of MySQL documentation

Then, lets see some ways which we can do this:

1. Using the RENAME TABLE command

As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

RENAME TABLE Syntax na Documentacao do MySQL

mysql> show tables;
+---------------------+
| Tables_in_oldSchema |
+---------------------+
| tb1                 |
| tb10                |
| tb2                 |
| tb3                 |
| tb4                 |
| tb5                 |
| tb6                 |
| tb7                 |
| tb8                 |
| tb9                 |
+---------------------+
10 rows in set (0.00 sec)

Now, let’s do a query to get our ALTER TABLE command:

mysql> SELECT CONCAT('RENAME TABLE ', GROUP_CONCAT( table_schema,'.',table_name, ' TO ','new_schema.',table_name,' '),';') as stmt FROM information_schema.TABLES WHERE table_schema LIKE 'oldSchema' GROUP BY table_schema\G
*************************** 1. row ***************************
stmt: RENAME TABLE oldSchema.tb10 TO new_schema.tb10 ,oldSchema.tb1 TO new_schema.tb1 ,oldSchema.tb9 TO new_schema.tb9 ,oldSchema.tb8 TO new_schema.tb8 ,oldSchema.tb7 TO new_schema.tb7 ,oldSchema.tb6 TO new_schema.tb6 ,oldSchema.tb5 TO new_schema.tb5 ,oldSchema.tb4 TO new_schema.tb4 ,oldSchema.tb3 TO new_schema.tb3 ,oldSchema.tb2 TO new_schema.tb2 ;
1 row in set (0.01 sec)

UPDATE:
if the output of the above query isn’t complete, that is because GROUP_CONCAT() function limit the result length, to change it run the bellow query:

SET SESSION group_concat_max_len = 4294967295;

It’s this, now we just need to run the returned statment as a new query to move all tables from the oldSchema to new one, don’t forget, first you need to create your new database after this you can run the returned statment as a new query.

2. using mysqldump

mysqldump [OPTIONS] --database oldSchema > oldSchema.sql
mysql new_schema < oldSchema.sql

Good, it’s this for today. enjoy!!!

Liked? Help and Share!
Posted in MySQL, mysqldump, Uncategorized | 7 Comments