Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. MySQL 5.7.8 introduced much improved version of mysqldump, It’s called “mysqlpump”, mysqlpump is much faster than mysqldump with parallel threads capabilities, There are many other compelling reasons for choosing mysqlpump over mysqldump, This blog is about how mysqlpump can be used for good. mysqlpump is relatively a new utility of MySQL and we are confident that Oracle MySQL will invest more to make mysqlpump efficient, we haven’t recommended mysqlpump in production for any of our customers till date, considering several concerns. The following below are mysqlpump features we are really excited about: Supports parallel MySQL logical backup, The resource usage efficiency and high performance backups (we love it !) Much better orchestration possible – You can backup selected databases, tables, stored programs and user accounts etc. By default mysqlpump will not backup performance_schema, sys schema, ndbinfo by default, You have to name them with –databases or –include-databases option mysqlpump does not dump INFORMATION_SCHEMA schema. Faster secondary indexes creation, The indexes created only after inserting rows !  mysqlpump examples  Plain simple backup using mysqlpump:  [root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees > employeebakup$(date '+%Y-%m-%H-%M-%S').sql Enter password: Dump progress: 1/4 tables, 0/630999 rows Dump progress: 2/6 tables, 541250/3919384 rows Dump progress: 4/6 tables, 1306627/3919384 rows Dump progress: 5/6 tables, 2128435/3919384 rows Dump progress: 5/6 tables, 3081685/3919384 rows Dump completed in 5309 milliseconds [root@localhost mysqlpump2018-06-23-25-49]# Using mysqlpump based backup with 6 threads:  [root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees --default-parallelism=6 > employeebakup$(date '+%Y-%m-%H-%M-%S').sql Enter password: Dump progress: 0/5 tables, 250/3477363 rows Dump progress: 2/6 tables, 606250/3919384 rows Dump progress: 3/6 tables, 1272103/3919384 rows Dump progress: 5/6 tables, 2028185/3919384 rows Dump progress: 5/6 tables, 2932185/3919384 rows Dump progress: 5/6 tables, 3864185/3919384 rows Dump completed in 5503 milliseconds [root@localhost mysqlpump2018-06-23-25-49]# Using mysqlpump to backup only selected databases, spawned 5 threads to backup employee and sakila database: [root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees --parallel-schemas=5:employees,sakila --default-parallelism=6 > bakup$(date '+%Y-%m-%H-%M-%S').sql Enter password: Dump progress: 1/6 tables, 0/3919384 rows Dump progress: 2/6 tables, 635250/3919384 rows Dump progress: 3/6 tables, 1354353/3919384 rows Dump progress: 5/6 tables, 2219935/3919384 rows Dump progress: 5/6 tables, 3066185/3919384 rows Dump completed in 5279 milliseconds [root@localhost mysqlpump2018-06-23-25-49]# Using mysqlpump to backup selected database and schema: [root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p --databases employees.titles > emp.titles$(date '+%Y-%m-%H-%M-%S').sql Enter password: Dump completed in 437 milliseconds [root@localhost mysqlpump2018-06-23-25-49]# Restore backup from mysqlpump Both mysqldump and mysqlpump generate MySQL logical backup in .SQL file so restoration is quiet an straightforward process. The post How to use mysqlpump for faster MySQL logical backup ? appeared first on MySQL Consulting, Support and Remote DBA Services By MinervaDB.
  2. Internet is full of simple shell backup scripts. Every DBA and system administrators (including myself) has written one. Bash is a bad tool for this job and in this post I will explain why. Let’s find out why the need to write anything arises at first place. There is a lot of great backup tools […] The post Simple shell script is bad idea appeared first on TwinDB.
  3. Continuing on the series of blog posts about ProxySQL. Today I will demonstrate how to put ProxySQL in front of your master and slave topology and distribute reads and writes between the servers, without the need of changing your application code. To install ProxySQL and some of its basic concepts, please read this blog post. On this tutorail, we will use 4 servers: ProxySQL (192.168.112.60) Node1 – Master (192.168.112.61) Node2 – Slave-1 (192.168.112.62) Node3 – Slave-3 (192.168.112.63)   Servers With ProxySQL already installed, we will connect into the admin interface, add our 3 servers and configure Replication Hostgroup. Basically, we will tell ProxySQL which HG will be our master and which HG will be our slaves. ProxySQL differs master and slaves based on server  read_only variable: $ mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='proxysql> ' #proxysql>  INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.61'); INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.62'); INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.63'); INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES (10, 20, 'Master / Slave App 1'); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; When we check the list of servers, we still see them all on HG 20, including our master: proxysql> SELECT hostgroup_id, hostname, status FROM mysql_servers; +--------------+----------------+--------+ | hostgroup_id | hostname | status | +--------------+----------------+--------+ | 20 | 192.168.112.61 | ONLINE | | 20 | 192.168.112.63 | ONLINE | | 20 | 192.168.112.62 | ONLINE | +--------------+----------------+--------+ 3 rows in set (0.00 sec) ProxySQL has a thread responsible to connect on each server lister at mysql_servers table and check the value of  read_only variable. On table mysql_server_read_only_log we can check the logs of this thread: proxysql> SELECT * FROM mysql_server_read_only_log LIMIT 3; +----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+ | hostname | port | time_start_us | success_time_us | read_only | error | +----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+ | 192.168.112.61 | 3306 | 1529175123875168 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) | | 192.168.112.62 | 3306 | 1529175123876409 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) | | 192.168.112.63 | 3306 | 1529175123877369 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) | +----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) As we can see above, the proxy cannot connect on our servers. For security reasons, we will change the default username and password which ProxySQL Thread uses to connect on our servers: UPDATE global_variables SET variable_value='p_monitor' WHERE variable_name='mysql-monitor_username'; UPDATE global_variables SET variable_value='M0n170Rpwd!' WHERE variable_name='mysql-monitor_password'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; Now we need to create this user on our master: CREATE USER p_monitor@192.168.112.60 IDENTIFIED BY 'M0n170Rpwd!'; With the user configured on both, ProxySQL and MySQL, we can verify at the admin interface that now we have 4 servers on our list: proxysql> SELECT hostgroup_id, hostname, status FROM mysql_servers ORDER BY hostgroup_id; +--------------+----------------+--------+ | hostgroup_id | hostname | status | +--------------+----------------+--------+ | 10 | 192.168.112.61 | ONLINE | | 20 | 192.168.112.61 | ONLINE | | 20 | 192.168.112.62 | ONLINE | | 20 | 192.168.112.63 | ONLINE | +--------------+----------------+--------+ 4 rows in set (0.01 sec) Our Master is listed on both HG’s. We can change this behaviour by setting mysql-monitor_writer_is_also_reader to false (it comes enabled by default). Users Next step is to configure our application user on ProxySQL. We can either manually create each user or import them all as described on this blog post. On this tutorial, I will demonstrate how to manually create the user. Remember that you need this user created on MySQL too and with the appropriate GRANTS in place : INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('marcelo', 'marcelo', 10); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; If you query mysql_users table you will be able to see the password in plaintext: proxysql> SELECT username, password FROM mysql_users; +----------+----------+ | username | password | +----------+----------+ | marcelo | marcelo | +----------+----------+ 1 row in set (0.00 sec) However, this is not the case when querying runtime_mysql_users table. Runtime table will have the password computed as a hash. For security reasons, every time we manually create a user on ProxySQL we need to save its runtime version back to main and also save the hashed version to disk: proxysql> SAVE MYSQL USERS FROM RUNTIME; SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) proxysql> SELECT username, password FROM mysql_users; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | marcelo | *8E36BAA4C91256FAEF957292B1C224C102754D25 | +----------+-------------------------------------------+ 1 row in set (0.00 sec) Query Rules Is that all ?? Almost, now is where the magic begins. Every time user  marcelo connets to ProxySQL, it will send all it’s queries to HG 10 (configured at  default_hostgroup field of  mysql_users table). HG 10 is mapped to our master. Now what we need to do is to tell ProxySQL to redirect our SELECT commands to HG 20, which is configured to balance the queries between all server, including our slaves. We will do this config by inserting rules on mysql_query_rules table . There is a small exception which we need to consider, SELECT . . . FOR UPDATE commands will place a lock on the records they read with the intent of update those lines. In this case, we need to make sure those queries will always reach our HG 10: INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,match_digest,apply) VALUES(1,'marcelo',10,1,'^SELECT.*FOR UPDATE',1); INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,match_digest,apply) VALUES(2,'marcelo',20,1,'^SELECT ',1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; Done, now ProxySQL is configured to do read and write split between our servers. All we need to do is to configure our application to connect to ProxySQL instead of connecting direct to our master. Remember that by default, ProxySQL application interface is bound on 6033 port. Below is a graph from PMM showing the difference of traffic being handled by a single master server versus the moment we change the traffic to be controlled by ProxySQL doing split of read and writes across master and slaves: As we can see, our node1(Master) had a high traffic compared to the other two servers, at the moment ProxySQL started to distribute the traffic we can see all 3 servers handling about the same amount of traffic. The same behaviour can be seen on load average graphic. Failover ProxySQL is capable to identify a failover has happened. As soon as the master is unavailable and a slave has been promoted to master, it will identify the slave is not configured with  read_only anymore and will change the slave server to HG 10. Important: ProxySQL is a proxy, it is responsible to redirect the traffic to the desired backend server once failover has happened. It will not perform the failover for your, such as execute  CHANGE MASTER TO on remaining slaves.   Delayed Slaves ProxySQL can stop send traffic to a slave if it is behind master for more than x seconds. In order to be able to check the status of the slave, ProxySQL monitor user will require REPLICATION CLIENT. We will add this grant to our user by executing below command on our master: GRANT REPLICATION CLIENT ON *.* TO p_monitor@192.168.112.60 Now, we will configure ProxySQL to shun traffic on slaves with more than 10 seconds: UPDATE mysql_servers SET max_replication_lag = 10 WHERE hostgroup_id=20; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; Done, now every time a slave gets behind, it will have its status changed to SHUNNED at runtime_mysql_servers tables: proxysql> SELECT * FROM runtime_mysql_servers; +--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.112.61 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.112.61 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | | | 20 | 192.168.112.63 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | | | 20 | 192.168.112.62 | 3306 | SHUNNED | 1 | 0 | 1000 | 10 | 0 | 0 | | +--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 4 rows in set (0.00 sec) We can check the lag ProxySQL got when checking the server by querying mysql_server_replication_lag_log table: proxysql> SELECT * FROM mysql_server_replication_lag_log WHERE hostname = '192.168.112.62' ORDER BY time_start_us DESC LIMIT 1; +----------------+------+------------------+-----------------+----------+-------+ | hostname | port | time_start_us | success_time_us | repl_lag | error | +----------------+------+------------------+-----------------+----------+-------+ | 192.168.112.62 | 3306 | 1529190811923215 | 953 | 604 | NULL | +----------------+------+------------------+-----------------+----------+-------+ 1 row in set (0.00 sec) Slave lag is monitored every mysql-monitor_replication_lag_interval millisecond (10 seconds by default).
  4. On my last post I gave a gasp about some key concepts of ProxySQL. Today I will demonstrate how we can import all users from MySQL to ProxySQL To accomplish this, we will make use of,proxysql-admin a tool developed by  Percona to help you manage and install ProxySQL. If you are using ProxySQL provided by Percona, this tool is already installed, otherwise, you can download it directly from Github. We will use the  --syncusers option, which will connect to the desired MySQL server, read all users from  mysql.user table and import or sync them to ProxySQL: proxysql-admin --cluster-username=[mysql-user] --cluster-password=[mysql-password] --cluster-port=[mysql-port] --cluster-hostname=[mysql-host] --syncusers This tool was originally designed to enhance the compatibility between ProxySQL and  Percona XtraDB Cluster, so, many terminology mentions cluster, however, the functionality demonstrate on this post is fully compatible with Master-Slave. Here is one example of the command and it’s output: $ proxysql-admin --cluster-username=root --cluster-password=sekret --cluster-port=3306 --cluster-hostname=192.168.112.61 --syncusers Syncing user accounts from Percona XtraDB Cluster to ProxySQL Synced Percona XtraDB Cluster users to the ProxySQL database! It worth mention that every time a new user is added or an existing user has its password changed, we also need to update the user on ProxySQL. With proxysql-admin, all we need to do on those cases is re-run the tool with  --syncusers  to get everything back in sync.
  5. Last time I was stumped by the MongoDB $gt: operator.  I wanted to look for restaurants in a certain Manhattan burough OR in a zipcode greater than a certain zipcode.  Well, I was getting different results between Mongo and MySQL.To > or Not To >, That Is the Query Lets say we have three records with the same key but the values are 1, 2, and "3". Yup, you got it two numerics and one string.  I would expect schema less data to be free flowing, not typed, and pretty much a free for all.  Whoops. Bad assumption on my part for Mongo use.I added three JSON documents into Mongo as can be seen below: Our three documents with the values of 1, 2, & "3" in Mongo And the same data into the MySQL Document Store: Our test data in the MySQL Document Store with the values of 1, 2, and "3" The Search Now search for items with a value greater than 1. MySQL handles this by returning two records: MySQL says there are two documents where the value of item is greater than 1 However Mongo differs: Mongo says ther is only one document with the value of item great than 1 What Happened?Mongo's reference manual under db.collection.find informs us that 'comparison operators only perform comparisons on documents where the BSON type of the target field matches the type of the query operand.' and silly me was trying to compare a numeric to a string.Now you can enforce data types in a Mongo collection by using schema validation.  But I thought the beauty of schema less data was that you did not have to normalize the data.Am I picking nits? Well, I have been pushing data around in computers for close to four decades now and wish I had a nickel (or other small coin of similar value in another curency) for every time I had to 'wash' my data to switch it from Type X to Type Y or reshuffle dates or even add/remove thousands separators.  To me schema less means that "3" and 3 are equal. At least until they are cast to a type. Next Time I have been getting some very good feedback on this subject and will delve into this more.