Archive

Articles taggués ‘MySQL’

MySQL – Migrate Users from Server to Server

22/03/2019 Comments off

mysql migrate usersSometimes we need to migrate our databases to a new MySQL server.  It is easy to move the databases, but without the users and their permissions, our new databases would be worthless.  Below is a step-by-step on migrating MySQL users to a new MySQL server

Step 1 – Create a Query List That We Can Use to Get Grants for All Users

I use these options so that I wouldn’t get any formatting characters that I would have to manually delete later.

  • -N skip column names in the output
  • -p password – Asks me to type the password so nobody can get it from the command line history
  • -s  silent mode – less formatting output that we don’t want like “|” and “-“

So, let’s get a list of the users in a query that we can use to get the grants.  Our query will be output into the “myfile” file

$ mysql -uroot -N -p -s > myfile
Enter password:
select Distinct CONCAT(‘show grants for `’, user, ‘`@`’, host, ‘`;’) as query from mysql.user;
quit

If we want to see what our query file look like, we can take a quick peek:

[root@classes-dev-mysql ~]# cat myfile
show grants for `user1`@`%`;
show grants for `user2`@`%`;
show grants for `user3`@`10.%`;
show grants for `user4`@`10.%`;
show grants for `jeff`@`10.%`;

Step 2 – Create the MySQL Grant File

We don’t have quite what we want and need yet.  We are looking for a query that will create all of our users on the new MySQL server.  We need to run the query that we just created and it will give us the query that we will use later to create the users.  It will create our grant permission statements in a file named “grantfile

[root@classes-dev-mysql ~]# mysql -uroot -N -p -s -r < myfile > grantfile
Enter password:

We can take a peek at what our grantfile contains:

$ cat grantfile
GRANT USAGE ON *.* TO ‘user1’@’%’ IDENTIFIED BY PASSWORD ‘5ea9af6g6t27032f’
GRANT ALL PRIVILEGES ON `database1`.* TO ‘user1’@’%’
GRANT USAGE ON *.* TO ‘user2’@’10.%’ IDENTIFIED BY PASSWORD ‘2a123b405cbfe27d’
GRANT SELECT ON `database1`.`table1` TO ‘user2’@’10.%’GRANT ALL PRIVILEGES ON *.* TO ‘user3’@’10.%’ IDENTIFIED BY PASSWORD ‘753af2za1be637ea’
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO ‘user3’@’10.%’ IDENTIFIED BY PASSWORD ’08ad9be605rfgcb’…

Step 3 – Create Users and Grant MySQL Permissions on the New MySQL Machine

Now we are done working on the source machine.  We need to copy our file named “grantfile” over to the new machine.

$ scp grantfile myuser@mysql2.uptimemadeeasy.com:/home/myuser

Next, we login to the destination or the new MySQL machine that we are building and run the “grantfile” in MySQL to create our users on the new MySQL machine.

$ mysql -uroot -p < ./grantfile

That’s it.  As long as our databases are named the same in the new MySQL, our users should be ready to use the copy of the databases in the new MySQL machine.

Source: Uptime Made Easy

Des requêtes SQL pour vous sauver la vie avec WordPress

18/03/2019 Comments off

sql wordpressVous avez changé votre blog WordPress de serveur et de domaine et là plus rien ne marche?

Vous voulez faire le ménage dans votre base de données ?

Et bien voici quelques exemples de requêtes SQL qui vous aiderons à faire tout ça rapidement et sans prise de tête.

Sauvegarde:

Avant de commencer la chose la plus importante à faire c’est de sauvegarder votre base de donnée.

Pour cela, plusieurs solutions s’offre à vous.

Soit vous utilisez directement une extensions WordPress tel que WP-DB-Backup ou WP-DBManager, ou alors vous pouvez le faire manuellement depuis l’interface de phpMyAdmin en suivant la procédure suivante:

  1. Connectez vous sur phpMyAdmin.
  2. Sélectionnez votre base WordPress.
  3. Cliquez sur « exporter » dans le menu en haut de la page.
  4. Sélectionnez les tables à sauvegarder.
  5. Sélectionnez SQL pour avoir un fichier de sortie en .sql.
  6. Cochez la case « Transmettre«
  7. Vous pouvez choisir un type de compression afin de réduire la taille de votre fichier.
  8. Cliquez sur « Exécuter » et le fichier devrait se télécharger.

phpmyadmin-export

Lire la suite…

MySQL Query Profiling

27/02/2019 Comments off

You can profile a query by doing following:

mysql> SET SESSION profiling = 1;
mysql> USE database_name;
mysql> SELECT * FROM table WHERE column = 'value';
mysql> SHOW PROFILES;

First line enables profiling for current mysql interactive session only. Global profiling is not recommended.

Second line selects database on which we need to fire query.

Third line is actual query. (do not use EXPLAIN here).

Fourth line shows list of recorded profiles. It’s output looks like:


mysql> SHOW PROFILES;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                             |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00008050 | SELECT DATABASE()                                                                                                                 |
|        2 | 0.00034975 | show databases                                                                                                                    |
|        3 | 0.00073850 | show tables                                                                                                                       |
|        4 | 0.00040525 | SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Above is list of queries profiled in current session.

You can get execution time breakdown by running another mysql query:

SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

It will print data like:


mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION       | SOURCE_FILE   | SOURCE_LINE |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
|        4 |   2 | starting                       | 0.000014 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | NULL                  | NULL          |        NULL |
|        4 |   3 | Waiting for query cache lock   | 0.000002 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | try_lock              | sql_cache.cc  |         646 |
|        4 |   4 | Waiting on query cache mutex   | 0.000001 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | try_lock              | sql_cache.cc  |         650 |
|        4 |   5 | checking query cache for query | 0.000057 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | send_result_to_client | sql_cache.cc  |        1853 |
|        4 |   6 | checking permissions           | 0.000003 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | check_access          | sql_parse.cc  |        5007 |
|        4 |   7 | checking permissions           | 0.000004 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | check_access          | sql_parse.cc  |        5007 |
|        4 |   8 | Opening tables                 | 0.000046 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | open_tables           | sql_base.cc   |        4944 |
|        4 |   9 | System lock                    | 0.000009 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_lock_tables     | lock.cc       |         299 |
|        4 |  10 | Waiting for query cache lock   | 0.000001 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | try_lock              | sql_cache.cc  |         646 |
|        4 |  11 | Waiting on query cache mutex   | 0.000021 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | try_lock              | sql_cache.cc  |         650 |
|        4 |  12 | init                           | 0.000032 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_select          | sql_select.cc |        2622 |
|        4 |  13 | optimizing                     | 0.000015 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | optimize              | sql_select.cc |         889 |
|        4 |  14 | statistics                     | 0.000057 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | optimize              | sql_select.cc |        1099 |
|        4 |  15 | preparing                      | 0.000017 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | optimize              | sql_select.cc |        1121 |
|        4 |  16 | executing                      | 0.000002 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | exec                  | sql_select.cc |        1879 |
|        4 |  17 | Sending data                   | 0.000099 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | exec                  | sql_select.cc |        2423 |
|        4 |  18 | end                            | 0.000003 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_select          | sql_select.cc |        2658 |
|        4 |  19 | query end                      | 0.000003 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_execute_command | sql_parse.cc  |        4686 |
|        4 |  20 | closing tables                 | 0.000007 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_execute_command | sql_parse.cc  |        4738 |
|        4 |  21 | freeing items                  | 0.000011 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_parse           | sql_parse.cc  |        5931 |
|        4 |  22 | logging slow query             | 0.000001 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | log_slow_statement    | sql_parse.cc  |        1625 |
|        4 |  23 | cleaning up                    | 0.000002 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | dispatch_command      | sql_parse.cc  |        1475 |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
22 rows in set (0.00 sec)

Source: rtcamp.com

How To Use MySQL Query Profiling

27/02/2019 Comments off

Introduction

MySQL query profiling is a useful technique when trying to analyze the overall performance of a database driven application. When developing a mid to large size application, there tends to be hundreds of queries distributed throughout a large code base and potentially numerous queries ran against the database per second. Without some sort of query profiling techniques, it becomes very difficult to determine locations and causes of bottlenecks and applications slow down. This article will demonstrate some useful query profiling techniques using tools that are built into MySQL server.

 

What is the MySQL slow query log?

The MySQL slow query log is a log that MySQL sends slow, potentially problematic queries to. This logging functionality comes with MySQL but is turned off by default. What queries are logged is determined by customizable server variables that allow for query profiling based on an application’s performance requirements. Generally the queries that are logged are queries that take longer than a specified amount of time to execute or queries that do not properly hit indexes.

 

Setting up profiling variables

The primary server variables for setting up the MySQL slow query log are:

slow_query_log			G 
slow_query_log_file			G 
long_query_time			G / S
log_queries_not_using_indexes	G
min_examined_row_limit		G / S

NOTE: (G) global variable, (S) session variable

slow_query_log – Boolean for turning the slow query log on and off.

slow_query_log_file – The absolute path for the query log file. The file’s directory should be owned by the mysqld user and have the correct permissions to be read from and written to. The mysql daemon will likely be running as `mysql` but to verify run the following in the Linux terminal:

 ps -ef | grep bin/mysqld | cut -d' ' -f1

The output will likely display the current user as well as the mysqld user. An example of setting the directory path /var/log/mysql:

cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql

long_query_time – The time, in seconds, for checking query length. For a value of 5, any query taking longer than 5s to execute would be logged.

log_queries_not_using_indexes – Boolean value whether to log queries that are not hitting indexes. When doing query analysis, it is important to log queries that are not hitting indexes.

min_examined_row_limit – Sets a lower limit on how many rows should be examined. A value of 1000 would ignore any query that analyzes less than 1000 rows.

The MySQL server variables can be set in the MySQL conf file or dynamically via a MySQL GUI or MySQL command line. If the variables are set in the conf file, they will be persisted when the server restarts but will also require a server restart to become active. The MySQL conf file is usually located in `/etc or /usr`, typically `/etc/my.cnf` or `/etc/mysql/my.cnf`. To find the conf file (may have to broaden search to more root directories):

find /etc -name my.cnf
find /usr -name my.cnf

Once the conf file has been found, simply append the desired values under the [mysqld] heading:

[mysqld]
….
slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

Again, the changes will not take affect until after a server restart, so if the changes are needed immediately then set the variables dynamically:

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';
mysql> SET SESSION long_query_time = 1;
mysql> SET SESSION min_examined_row_limit = 100;

To check the variable values:

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
mysql> SHOW SESSION VARIABLES LIKE 'long_query_time';

One drawback to setting MySQL variables dynamically is that the variables will be lost upon server restart. It is advisable to add any important variables that you need to be persisted to the MySQL conf file.

NOTE: The syntax for setting variables dynamically via SET and placing them into the conf file are slightly different, e.g. `slow_query_log` vs. `slow-query-log`. View MySQL’s dynamic system variables page for the different syntaxes. The Option-File Format is the format for the conf file and System Variable Name is the variable name for setting the variables dynamically.

Lire la suite…

Using MySQLTuner to Optimize MySQL

26/02/2019 Comments off

Tweaking MySQL is something you need to do regularly. Unlike PHP & Nginx tweaking, this is not a set & forget job!

We will use mysqltuner for tweaking mysql on a regular basis.

Tweaking MySQL default config first

Open /etc/mysql/my.cnf file & scroll down to [mysqld] section.

You will see many settings & some config variables. Some values are global while some are per-thread values. Its important because if you change something likejoin_buffer_size from 2M to 4M, it can shoot-up mysql’s max memory utilization by 300M memory (as per default 150 mysql’s max_connections value)

To start with, adjust following values:

max_connections = 50     #default is 150
wait_timeout = 30        #default is 28800

You can leave remaining as it is. Mysqltuner will guide you further.

Don’t forget to restart mysql. Command: service mysql restart

Using mysqltuner

If you are following our setup, you may already have mysqltuner installed. Otherwise run apt-get install mysqltuner on Ubuntu. Non-ubuntu guys can get it from here. It’s just a perl script!

When you run mysqltuner, it will show you a report with many suggestions. Just follow them. Exact suggestion will vary so its hard to cover all of them here. Rather I will give you some notes some of them are offered by mysqltuner itself.

Notes:

  1. Run mysqltuner after 24 hours. It you don’t, it will remind you by showing “MySQL started within last 24 hours – recommendations may be inaccurate.” Reason: mysqltuner recommendation may prove inaccurate.
  2. If it asks you to change value oftmp_table_size ormax_heap_table_size variable, make sure you change both and keep them equal. These are global values so feel free to increase them by large chunks (provided you have enough memory on server)
  3. If it asks you to tweak join_buffer_size, tweak in small chunks as it will be multiplied by value ofmax_connections.
  4. If it asks you to increase innodb_buffer_pool_size, make it large. Ideally, it should be large enough to accomodate your all innodb databases. If you do not have enough RAM consider buying some. Otherwise try to delete unwanted database. Do not ignore this as it can degrade performance significantly.

Apart from above, always keep an eye on following lines in Performance Metrics section of mysqltuner report:

[--] Total buffers: 2.6G global + 130.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 15.3G (48% of installed RAM)
[OK] Highest usage of available connections: 81% (81/100)

Try to keep maximum possible memory less than 50%. Other lines can tell you, if your site is using too “less” mysql connections. In that case, you can reducemax_connections and increase other buffers more generously.

Also, whenever you make changes to mysql config and restart mysql server, always run mysqltuner immediately to check if by mistake you haven’t made maximum possible memory usage too high! Ignore any other suggestion it will give for next 24-hours!

mysqltuner & automatic password

As we use mysqltuner many times, it will be convenient use something like this.

Source: rtcamp.com