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 | 2 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 | 1 Comment

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 | 29 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

MySQL replication – Master – Slave

To explain the replication, we will use the article Installing MySQL 5.5 Part 2 – Multiple instances with mysqld_multi because we will need 2 MySQL’s running on our server

Let’s activate the binary logs on our server, creating a file named my_rep.cnf with the follow content:

[mysqld_multi]
mysqld = /mysql/mysql/bin/mysqld_safe
mysqladmin = /mysql/mysql/bin/mysqladmin
[mysqld1]
port = 3306
datadir = /mysql/3306/data
socket = /mysql/3306/mysql.sock
log-error = /mysql/3306/logs/mysqld.log
pid-file = /mysql/3306/mysqld.pid
server_id = 1
#LOGS
log-bin = /mysql/3306/logs/server1_bin.log
log-bin-index = /mysql/3306/logs/server1_log-bin.index
expire_logs_days = 7
binlog-format = MIXED
[mysqld2]
port = 3307
datadir = /mysql/3307/data
socket = /mysql/3307/mysql.sock
log-error = /mysql/3307/logs/mysqld.log
pid-file = /mysql/3307/mysqld.pid
server_id = 2
#LOGS
relay-log = /mysql/3307/logs/server2.relay_log
relay-log-index = /mysql/3307/logs/server2.relay_log_index
expire_logs_days = 7

Done this, let’s start the MySQL service like our article:

cd /mysql/mysql
mysqld_multi --defaults-file=/mysql/my_rep.cnf start

Now, we will connect to server 3306 and create a new user named ‘replication’ with password ‘replication’ and rights to replication.

mysql -u user -p -h 127.0.0.1
mysql> GRANT REPLICATION SLAVE ON *.* TO replication@127.0.0.1 IDENTIFIED BY 'replication';
mysql> FLUSH PRIVILEGES;

Now we need to connect on our slave server and run a start command to start our replication:

mysql -u usuario -p -h 127.0.0.1 --port 3307
mysql> CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_USER = 'replication', MASTER_PASSWORD = 'replication', MASTER_PORT = 3306, MASTER_LOG_FILE = 'server1_bin.000001', MASTER_LOG_POS = 0;
mysql> START SLAVE;

Let’s explain the CHANGE MASTER TO command:

MASTER_USER = user which will connect to master server
MASTER_PASSWORD = user password which will connect to master server
MASTER_PORT = the mysql master server port
MASTER_LOG_FILE = binary log file name (we set it to server1_bin.log on .cnf file, like we can see, MySQL create file with numeric index which increase each time which server is restarted or exceed the limit set on our configuration by max_binlog_size key which by default is 1GB
MASTER_LOG_POS = the position which the slave will start to search inside the binlog

And how to monitor the replication??? Simple with the command

SHOW SLAVE STATUS;

The replication works with 2 threads:

Slave_IO = Is the thread responsible to get all sql commands from master and save them at the slave disc
Slave_SQL = Is the thread responsible to run the commands which Slave_IO get from master server

Now, I will explain 5 important status from

SHOW SLAVE STATUS;

Slave_IO_Running = Tell us if the IO thread is running, we need to see a Yes on this line
Slave_SQL_Running = Tell us if the SQL thread is running, we need to see a Yes on this line
Seconds_Behind_Master = Tell us if our slave is delayed in relation to our master, here we need to see a 0(zero)
Last_IO_Error = In case we have a problem with IO thread, this line will show us the problem
Last_SQL_Error = In case we have a problem with SQL thread, this line will show us the problem

And is it, now he have a Master server replication their data to a SLAVE server.

To see more details about replication on MySQL, please read MySQL Documentation

Liked? Help and Share!
Posted in binlog, MySQL, replication | 11 Comments