Menu Close

Author: marceloaltmann

MySQL force user change password

Since version 5.6.6 MySQL has introduced the capability to force an user to change their password. You can archive that by issue ALTER USER PASSWORD EXPIRE statement. ALTER USER ‘marcelo’@’localhost’ PASSWORD EXPIRE; Next time marcelo tries to login from localhost it will block all statements rather then SET PASSWORD; mysql> \s ERROR 1820 (HY000): You must SET PASSWORD before executing this statement To remove this block, just issue an : SET PASSWORD = PASSWORD(‘pwd’); #Or use the hash received from SELECT PASSWORD(‘pwd’); SET PASSWORD = ‘*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD’; But be careful, if you expire the password for an account, you won’t be able to login with any client version before 5.6.10(GA): ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords. . . .

Liked? Help and Share!

Why Does MySQL Replication Delays?

These days I’ve answer some questions about replication lag, and I realized that most of people does not correctly understand how this process works internally on MySQL, and why does delays happen: MySQL replication: most important config parameters for performance on slave server? mysql replication delay very serious See the bellow image, it represents asynchronous replication on MySQL, I highly recommend you to read my other post: “How Does MySQL Replication Works?” How you can see, there is a big difference related to the entry point of data on master and slave, while the master has multiple simultaneously threads inserting/updating/deleting data, on the slave is just a single one responsible for deal with all these transactions Let’s imagine that one transaction (an UPDATE for example) took 50 seconds to be . . .

Liked? Help and Share!

How Does MySQL Replication Works?

See the bellow image, it represents how asynchronous replication works on MySQL Bearing in mind the number on the image, let’s see how the process works: Item 1 in the image represents the clients executing queries on master, note that the master is capable to handle multiple simultaneous connections (it can be configurable by max_connections variable).  Master process these queries, and save it to his binary log(item number 2 in the image), then it can later on be replied on the slave. The slave has 2 threads reubuntu mount ext4 partitionsponsible to deal with replication : IO_THREAD – is responsible to connect to the Master, ask for new transaction (item number 3 in the image) and save them to his own log (relay log, item number 4 in the image). . . .

Liked? Help and Share!

Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format = STATEMENT | MIXED

Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format=STATEMENT|MIXED Since version 5.1.64 MySQL introduces a new variable named slave_max_allowed_packet, which was introduced to allow large updates using row-based replication do not cause replication to fail when exceeded max_allowed_packet. The problem is if you have you replication using binlog_format=STATEMENT or binlog_format=MIXED it ignores this option and use as limit for queries what is on max_allowed_packet variable but still reporting on slave_max_allowed_packet causing the IO_THREAD to report the wrong message. Solution: Run the follow query on master: master> SHOW VARIABLES LIKE 'binlog_format'; If it return STATEMENT or MIXED, you need to change the value for max_allowed_packet, a good option if possible is to have this variable equally configured on master and slave. http://bugs.mysql.com/bug.php?id=68490 http://bugs.mysql.com/bug.php?id=69104 Liked? Help and Share!

Liked? Help and Share!

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 – . . .

Liked? Help and Share!

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

Liked? Help and Share!

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

Liked? Help and Share!

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 *************************** . . .

Liked? Help and Share!

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

Liked? Help and Share!