Last week, durring MySQL Connect, MySQL 5.7.2 DMR was launched, one of the new functionality is the multi source replication. At the moment, MySQL can have only one master per slave (you can archive multi source replication via some hacks, but like the name says, it’s a hack). See how to configure here To clarify, there is a difference between multi-master replication and multi source replication, see the bellow pictures to understand the difference: Multi Master Replication – In the above picture, we have 2 master’s and 1 slave, where, master 1 is master of master 2, master 2 is master of master 1 and also of slave, in order to slave receive updates from master 1, all this updates must past to master 2 to later one be replied . . .
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. . . .
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 . . .
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). . . .
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!
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 – . . .
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 . . .
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 . . .
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 *************************** . . .
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 > . . .