MySQL Document Store – a quick-guide to storing JSON documents in MySQL using JavaScript and Python (and even SQL!)

October 24, 2019 Leave a comment

MySQL introduced a JSON data type in version 5.7, and expanded the functionality in version 8.0.

Besides being able to store native JSON in MySQL, you can also use MySQL as a document store (doc store) to store JSON documents. And, you can use NoSQL CRUD (create, read, update and delete) commands in either JavaScript or Python.

This post will show you some of the basic commands for using MySQL as a document store.

Requirements

To use MySQL as a document store, you will need to use the following server features:

The X Plugin enables the MySQL Server to communicate with clients using the X Protocol, which is a prerequisite for using MySQL as a document store. The X Plugin is enabled by default in MySQL Server as of MySQL 8.0. For instructions to verify X Plugin installation and to configure and monitor X Plugin, see Section 20.5, “X Plugin”.

The X Protocol supports both CRUD and SQL operations, authentication via SASL, allows streaming (pipelining) of commands and is extensible on the protocol and the message layer. Clients compatible with X Protocol include MySQL Shell and MySQL 8.0 Connectors.

Clients that communicate with a MySQL Server using X Protocol can use X DevAPI to develop applications. X DevAPI offers a modern programming interface with a simple yet powerful design which provides support for established industry standard concepts. This chapter explains how to get started using either the JavaScript or Python implementation of X DevAPI in MySQL Shell as a client. See X DevAPI for in-depth tutorials on using X DevAPI.
(source: https://dev.mysql.com/doc/refman/8.0/en/document-store.html)

And, you will need to have MySQL version 8.0.x (or higher) installed, as well as the MySQL Shell version 8.0.x (or higher). For these examples, I am using version 8.0.17 of both. (You could use version 5.7.x, but I have not tested any of these commands in 5.7.x)

Starting MySQL Shell (mysqlsh)

When starting MySQL Shell (Shell), you have two session options. The default option is mysqlx (‐‐mx), and this allows the session to connect using the X Protocol. The other option when starting Shell is ‐‐mysql, which establishes a “Classic Session” and connects using the standard MySQL protocol. For this post, I am using the default option of mysqlx (‐‐mx). There are other MySQL Shell command-line options for the X Protocol available. And, there are specific X Protocol variables which may need to be set for the mysqlx connection.

Here is a list of all of the MySQL Shell commands and their shortcuts (for MySQL version 8.0).
(source: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-commands.html)

Command	Alias/Shortcut	Description
\help	\h or \?	Print help about MySQL Shell, or search the online help.
\quit	\q or \exit	Exit MySQL Shell.
\		In SQL mode, begin multiple-line mode. Code is cached and executed when an empty line is entered.
\status	\s	Show the current MySQL Shell status.
\js		Switch execution mode to JavaScript.
\py		Switch execution mode to Python.
\sql		Switch execution mode to SQL.
\connect	\c	Connect to a MySQL Server.
\reconnect		Reconnect to the same MySQL Server.
\use	\u	Specify the schema to use.
\source	\.	Execute a script file using the active language.
\warnings	\W	Show any warnings generated by a statement.
\nowarnings	\w	Do not show any warnings generated by a statement.
\history		View and edit command line history.
\rehash		Manually update the autocomplete name cache.
\option		Query and change MySQL Shell configuration options.
\show		Run the specified report using the provided options and arguments.
\watch		Run the specified report using the provided options and arguments, and refresh the results at regular intervals.
\edit	\e	Open a command in the default system editor then present it in MySQL Shell.
\system	\!	Run the specified operating system command and display the results in MySQL Shell.

To start the MySQL Shell (Shell), you simply execute the mysqlsh command from a terminal window. The default mode is JavaScript (as shown by the JS in the prompt).

$ mysqlsh
MySQL Shell 8.0.17-commercial

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.

MySQL JS

By starting Shell without any variables, you will need to connect to a database instance. You do this with the \connect command, or you can use the \c shortcut. The syntax is \c user@ip_address:

MySQL JS \c root@127.0.0.1
Creating a session to 'root@127.0.0.1'
Fetching schema names for autocompletion. . . Press ^C to stop.
Your MySQL connection id is 16 (X protocol)
Server version: 8.0.17-commercial MySQL Enterprise Server – Commercial
No default schema selected; type \use to set one.
MySQL 127.0.0.1:33060+ ssl JS

Or, to start Shell with the connection information, specify the user and host IP address. The syntax is mysqlsh user@ip_address:

$ mysqlsh root@127.0.0.1
MySQL Shell 8.0.17-commercial

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@127.0.0.1'
Fetching schema names for autocompletion. . . Press ^C to stop.
Your MySQL connection id is 18 (X protocol)
Server version: 8.0.17-commercial MySQL Enterprise Server – Commercial
No default schema selected; type \use to set one.
MySQL 127.0.0.1:33060+ ssl JS

You may find a list of all of the command-line options at https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html.

The Shell prompt displays the connection information, whether or not you are using ssl, and your current mode (there are three modes – JavaScript, Python and SQL). In the earlier example, you are in the (default) JavaScript mode. You can also get your session information with the session command:

MySQL 127.0.0.1:33060+ ssl JS session
<Session:root@127.0.0.1:33060>

All of these commands are case-sensitive, so if you type an incorrect command, you will see the following error:

MySQL 127.0.0.1:33060+ ssl JS Session
ReferenceError: Session is not defined

Here is how you switch between the three modes: – JavaScript, Python and SQL.

MySQL 127.0.0.1:33060+ ssl JS    \sql
Switching to SQL mode. . . Commands end with ;
MySQL 127.0.0.1:33060+ ssl SQL    \py
Switching to Python mode. . .
MySQL 127.0.0.1:33060+ ssl Py    \js
Switching to JavaScript mode. . .
MySQL 127.0.0.1:33060+ ssl JS

There are also several different output formats. You may change the output using the shell.options.set command. The default is table. Here are examples of each one, using the same command:

table output format

MySQL 127.0.0.1:33060+ ssl JS shell.options.set('resultFormat','table')

JSON output format

MySQL 127.0.0.1:33060+ ssl JS shell.options.set('resultFormat','json')

MySQL 127.0.0.1:33060+ ssl JS    session.runSql('select user, host from mysql.user limit 2')
{
    “user”: “mysql.infoschema”,
    “host”: “localhost”
}
{
    “user”: “mysql.session”,
    “host”: “localhost”
}
2 rows in set (0.0005 sec)

tabbed format

MySQL 127.0.0.1:33060+ ssl JS shell.options.set('resultFormat','tabbed')

MySQL 127.0.0.1:33060+ ssl JS    session.runSql('select user, host from mysql.user')
user     host
mysql.infoschema     localhost
mysql.session     localhost
mysql.sys     localhost
root     localhost
4 rows in set (0.0004 sec)

vertical format

MySQL 127.0.0.1:33060+ ssl JS shell.options.set('resultFormat','vertical')

MySQL 127.0.0.1:33060+ ssl JS session.runSql('select user, host from mysql.user order by user desc')
*************************** 1. row ***************************
user: mysql.infoschema
host: localhost
*************************** 2. row ***************************
user: mysql.session
host: localhost
*************************** 3. row ***************************
user: mysql.sys
host: localhost
*************************** 4. row ***************************
user: root
host: localhost
4 rows in set (0.0005 sec)

MySQL Shell – Create & Drop Schema

Note: With the MySQL Doc Store, the terms to describe the database, table and rows are different. The database is called the schema (even thought the doc store is “schema-less”). The tables are called collections, and the rows of data are called documents.

To create a schema named test1, use the createSchema command:

MySQL 127.0.0.1:33060+ ssl JS session.createSchema("test1")
<Schema:test1>

To get a list of the current schemas, use the getSchemas command:

MySQL 127.0.0.1:33060+ ssl JS session.getSchemas()
[
<Schema:information_schema>,
<Schema:mysql>,
<Schema:performance_schema>,
<Schema:sys>,
<Schema:test1>
]

Also, you can run SQL commands inside of the Doc Store – something you can't natively do with most other NoSQL databases. Instead of using the getSchemas command, you can issue a SHOW DATABASES SQL command using the runSql NoSQL command.

To drop a schema named test1, you use the dropSchema command:

MySQL 127.0.0.1:33060+ ssl JS session.dropSchema("test1")

Just like with MySQL, you have to select the schema (database) you want to use. This shows you how to create a schema and set it as your default schema. With the \use command, you are really setting a variable named db to equal the default schema. So, after you have set your schema with the \use command, when you issue the db command, you can see the default schema.

MySQL 127.0.0.1:33060+ ssl JS session.createSchema("workshop")

MySQL 127.0.0.1:33060+ ssl JS \use workshop
Default schema `workshop` accessible through db.

MySQL 127.0.0.1:33060+ ssl workshop JS db
<Schema:workshop>

To change the value of the variable db, you may use the \use command or you may set the value of db using the var (variable) command. The command session.getSchema will return a schema value, but it does not automatically set the db variable value.)

MySQL 127.0.0.1:33060+ ssl JS \use workshop
Default schema `workshop` accessible through db.

MySQL 127.0.0.1:33060+ ssl workshop JS db
<Schema:workshop>

MySQL 127.0.0.1:33060+ ssl workshop JS session.getSchema('mysql');
<Schema:mysql>

MySQL 127.0.0.1:33060+ ssl workshop JS db
<Schema:workshop>

MySQL 127.0.0.1:33060+ ssl workshop JS var db = session.getSchema('mysql');

MySQL 127.0.0.1:33060+ ssl mysql JS db
<Schema:mysql>

You can also create your own variables. Here is an example of setting the variable sdb to equal the SQL command SHOW DATABASES:

MySQL 127.0.0.1:33060+ ssl workshop JS var sdb = session.runSql('show databases')

But the variables are only good for your current session. If you quit Shell, log back in, and re-run the variable, you will get an error stating the variable is not defined.

MySQL 127.0.0.1:33060+ ssl workshop JS \q
Bye!

# mysqlsh root@127.0.0.1
. . .

MySQL 127.0.0.1:33060+ ssl JS sdb
ReferenceError: sdb is not defined

After you have created your schema (database), and selected it via \use command, then you can create a collection (table) and insert JSON documents into the collection. I will create a collection named test1.

MySQL 127.0.0.1:33060+ ssl JS \use workshop

MySQL 127.0.0.1:33060+ ssl workshop JS db.createCollection("test1")

You can get a list of collections by using the getCollections command, and you can also execute SQL commands with the session.runSql command. The getCollections command is the same as the SQL command SHOW TABLES.

MySQL 127.0.0.1:33060+ ssl workshop JS db.getCollections()
[
<Collection:test1>
]

MySQL 127.0.0.1:33060+ ssl workshop JS session.runSql('SHOW TABLES')
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| Tables_in_workshop |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| test1 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
1 row in set (0.0001 sec)

To drop a collection, use the dropCollection command. You can verify the collection was dropped by using the getCollections command again.

MySQL 127.0.0.1:33060+ ssl workshop JS db.dropCollection("test1")
MySQL 127.0.0.1:33060+ ssl workshop JS db.getCollections()
[]

If you have not selected a default database, you may also specify the schema name prior to the collection name (which is the same type of syntax when you create a table inside of a database in MySQL). You will notice the output is different, as when you specify the schema name before the collection name, that schema name is returned as well.

MySQL 127.0.0.1:33060+ ssl workshop JS db.createCollection("foods")
<Collection:foods>

MySQL 127.0.0.1:33060+ ssl JS db.createCollection("workshop.foods2")
<Collection:workshop.foods2>

To add a JSON document to a collection, you use the add command. You must specify a collection prior to using the add command. (You can't issue a command like workshop.foods.add.) You can add the document with or without the returns and extra spaces (or tabs). Here the JSON document to add to the collection named workshop:

{
    Name_First: "Fox",
    Name_Last: "Mulder",
    favorite_food: {
        Breakfast: "eggs and bacon",
        Lunch: "pulled pork sandwich",
        Dinner: "steak and baked potato"
   }
}

Note: Some JSON formats require double quotes around the keys/strings. In this example, Name_First has double quotes in the first example, and the second example doesn’t have double quotes. Both formats will work in Shell.

The -> in the examples below is added by Shell. You don’t need to type this on the command line.

MySQL 127.0.0.1:33060+ ssl project JS    db.foods.add({
                                          -> "Name_First": "Steve"})
                                          ->
Query OK, 1 item affected (0.0141 sec)
MySQL 127.0.0.1:33060+ ssl project JS    db.foods.add({
                                          -> Name_First: "Steve"})
                                          ->
Query OK, 1 item affected (0.0048 sec)

Here are examples of each method of adding a JSON document – one where all of the data in the JSON document is on one line, and another where the JSON document contains data on multiple lines with returns and spaces included.

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.add({Name_First: "Fox", Name_Last: "Mulder", favorite_food: {Breakfast: "eggs and bacon", Lunch: "pulled pork sandwich", Dinner: "steak and baked potato"}})
Query OK, 1 item affected (0.0007 sec)

MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.add( {
                                          ->  Name_First: "Fox",
                                          ->  Name_Last: "Mulder",
                                          ->  favorite_food: {
                                          ->      Breakfast: "eggs and bacon",
                                          ->      Lunch: "pulled pork sandwich",
                                          ->      Dinner: "steak and baked potato"
                                          ->  } } )
                                          ->
Query OK, 1 item affected (0.0005 sec)

So far, I have created a schema, and a collection, and I have added one document to the collection. Next, I will show you how to perform searches using the find command.

Searching Documents

To find all records in a collection, use the find command without specifying any search criteria:

MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find()
{
    "_id": "00005d6fc3dc000000000027e065",
    "Name_Last": "Mulder",
    "Name_First": "Fox",
    "favorite_food": {
        "Lunch": "pulled pork sandwich",
        "Dinner": "steak and baked potato",
        "Breakfast": "eggs and bacon"
    }
}
1 document in set (0.0002 sec)

Note: The _id key in the output above is automatically added to each document and the value of _id cannot be changed. Also, an index is automatically created on the _id column. You can check the index using the SQL SHOW INDEX command. (To make the output easier to view, I switched the format to vertical and I switched it back to table)

MySQL 127.0.0.1:33060+ ssl workshop JS     shell.options.set('resultFormat','vertical')
MySQL 127.0.0.1:33060+ ssl workshop JS    session.runSql('SHOW INDEX FROM workshop.foods')
*************************** 1. row ***************************
        Table: foods
   Non_unique: 0
     Key_name: PRIMARY
Seq_in_index: 1
  Column_name: _id
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.0003 sec)
MySQL 127.0.0.1:33060+ ssl workshop JS     shell.options.set('resultFormat','table')

Here is how you perform a search by specifying the search criteria. This command will look for the first name of Fox via the Name_First key.

MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find('Name_First = "Fox"')
{
    "_id": "00005d6fc3dc000000000027e065",
    "Name_Last": "Mulder",
    "Name_First": "Fox",
    "favorite_food": {
        "Lunch": "pulled pork sandwich",
        "Dinner": "steak and baked potato",
        "Breakfast": "eggs and bacon"
    }
}
1 document in set (0.0004 sec)

And, if your search result doesn't find any matching documents, you will get a return of Empty set and the time it took to run the query:

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.find('Name_First = "Jason"')
Empty set (0.0003 sec)

The first search returned all of the keys in the document because I didn’t specify any search criteria inside the find command – db.foods.find(). This example is how you retrieve just a single key inside a document. And notice the key favorite_food contains a list of sub-keys inside of a key.

MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find('Name_First = "Fox"').fields('favorite_food')
{
    “favorite_food”: {
            “Lunch”: “pulled pork sandwich”,
            “Dinner”: “steak and baked potato”,
            “Breakfast”: “eggs and bacon”
        }
}
1 document in set (0.0004 sec)

To return multiple keys, add the additional keys inside the fields section, separated by a comma.

MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find('Name_First = "Fox"').fields("favorite_food", "Name_Last")
{
    “Name_Last”: “Mulder”,
    “favorite_food”: {
            “Lunch”: “pulled pork sandwich”,
            “Dinner”: “steak and baked potato”,
            “Breakfast”: “eggs and bacon”
        }
}
1 document in set (0.0003 sec)

Note: The fields are returned in the order they are stored in the document, and not in the order of the fields section. The query field order does not guarantee the same display order.

To return a sub-key from a list, you add the key prior to the sub-key value in the fields section. This search will return the value for the Breakfast sub-key in the favorite_food key.

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.find( 'Name_First = "Fox"' ).fields( "favorite_food.Breakfast" )
{
“favorite_food.Breakfast”: “eggs and bacon”
}
1 document in set (0.0002 sec)

Modifying Documents

To modify a document, you use the modify command, along with search criteria to find the document(s) you want to change. Here is the original JSON document.

{
    “_id”: “00005d6fc3dc000000000027e065”,
    “Name_Last”: “Mulder”,
    “Name_First”: “Fox”,
    “favorite_food”: {
        “Lunch”: “pulled pork sandwich”,
        “Dinner”: “steak and baked potato”,
        “Breakfast”: “eggs and bacon”
    }
}

I am going to change Fox's favorite_food sub-keys to Lunch being “soup in a bread bowl” and Dinner to “steak and broccoli”.

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.modify("Name_First = 'Fox'").set("favorite_food", {Lunch: "Soup in a bread bowl", Dinner: "steak and broccoli"})
Query OK, 0 items affected (0.0052 sec)

I can see the changes by issuing a find command, searching for Name_First equal to “Fox”.

MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find('Name_First = "Fox"')
{
    "_id": "00005d6fc3dc000000000027e065",
    "Name_Last": "Mulder",
    "Name_First": "Fox",
    "favorite_food": {
        “Lunch”: "Soup in a bread bowl",
        "Dinner": "steak and broccoli"
    }
}
1 document in set (0.0004 sec)

Notice that the Breakfast sub-key is no longer in the list. This is because I changed the favorite_food list to only include Lunch and Dinner.

To change only one sub-key under favorite_food, such as Dinner, I can do that with the set command:

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.modify("Name_First = 'Fox'").set('favorite_food.Dinner', 'Pizza')
Query OK, 1 item affected (0.0038 sec)

MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find("Name_First='Fox'")
{
    "_id": "00005d8122400000000000000002",
    "Name_Last": "Mulder",
    "Name_First": "Fox",
    "favorite_food": {
        "Lunch": "Soup in a bread bowl",
        "Dinner": "Pizza",
    }
}

If I want to remove a single sub-key under favorite_food, I can use the unset command. I will remove the Dinner sub-key:

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.modify("Name_First = 'Fox'").unset("favorite_food.Dinner")
Query OK, 1 item affected (0.0037 sec)

The key favorite_food contained a list, but a key can also contain an array. The main format difference in an array and a list is an array has opening and closing square brackets [ ]. The brackets go on the outside of the values for the array.

Here is the original document with a list for favorite_food:

db.foods.add( {
    Name_First: "Fox",
    Name_Last: "Mulder",
    favorite_food: {
        Breakfast: "eggs and bacon",
        Lunch: "pulled pork sandwich",
        Dinner: "steak and baked potato"
}
}
)

I am going to delete the document for Fox, confirm the deletion occurred and insert a new document – but this time, I am going to use an array for favorite_food (and notice the square brackets).

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.remove('Name_First = "Fox"')
Query OK, 1 item affected (0.0093 sec)
MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.find("Name_First='Fox'")
Empty set (0.0003 sec)

MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.add( {
                                         ->     Name_First: "Fox",
                                         ->     Name_Last: "Mulder",
                                         ->     favorite_food: [ {
                                         ->        Breakfast: "eggs and bacon",
                                         ->        Lunch: "pulled pork sandwich",
                                         ->        Dinner: "steak and baked potato"
                                         ->   } ]
                                         -> }
                                         -> )
                                         ->
Query OK, 1 item affected (0.0078 sec)
MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find("Name_First='Fox'")
{
    "_id": "00005da09064000000000027e068",
    "Name_Last": "Mulder",
    "Name_First": "Fox",
    "favorite_food": [
        {
            "Lunch": "pulled pork sandwich",
            "Dinner": "steak and baked potato",
            "Breakfast": "eggs and bacon"
        }
    ]
}
1 document in set (0.0004 sec)

When dealing with an array, I can modify each element in the array, or I can add another array element. I am going to add Fox's favorite Snack to the array with the arrayAppend command:

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.modify("Name_First = 'Fox'").arrayAppend("favorite_food", {Snack: "Sunflower seeds"})
Query OK, 1 item affected (0.0048 sec)

Rows matched: 1 Changed: 1 Warnings: 0
MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find("Name_First='Fox'")
{
    “_id”: “00005da09064000000000027e068”,
    “Name_Last”: “Mulder”,
    “Name_First”: “Fox”,
    “favorite_food”: [
        {
            “Lunch”: “pulled pork sandwich”,
            “Dinner”: “steak and baked potato”,
            “Breakfast”: “eggs and bacon”
        },
        {
            “Snack”: “Sunflower seeds”
        }
    ]
}
1 document in set (0.0004 sec)

The key favorite_food now contains an array with two separate values in it. The sub-keys in the array position favorite_food[0] contains values for Lunch, Breakfast and Dinner values, while the array position favorite_food[1] only contains the Snack value.

Note: If you aren't familiar with arrays, an array is like a list that contains elements. Elements of the array are contained in memory locations relative to the beginning of the array. The first element in the array is actually zero (0) elements away from the beginning of the array. So, the placement of the first element is denoted as array position zero (0) and this is designated by [0]. Most programming languages have been designed this way, so indexing from zero (0) is pretty much inherent to most languages.

I can now delete an element in an array with the arrayDelete command. I am going to remove the Snack array member, which is favorite_food[1].

MySQL 127.0.0.1:33060+ ssl workshop JS db.foods.modify("Name_First = 'Fox'").arrayDelete("$.favorite_food[1]")
Query OK, 1 item affected (0.0035 sec)

Rows matched: 1 Changed: 1 Warnings: 0
MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find("Name_First='Fox'")
{
    "_id": "00005da09064000000000027e068",
    "Name_Last": "Mulder",
    "Name_First": "Fox",
    "favorite_food": [
        {
            "Lunch": "pulled pork sandwich",
            "Dinner": "steak and baked potato",
            "Breakfast": "eggs and bacon"
        }
    ]
}
1 document in set (0.0004 sec)

Modifying a document – adding a key

If I want to add an extra key, I need to add a few more variables and their values. I will need to define my schema and collection. Then I can use the patch command to add a key to an existing document.

I am going to add a middle name to Fox's document.

MySQL 127.0.0.1:33060+ ssl workshop JS var schema = session.getSchema('workshop')

MySQL 127.0.0.1:33060+ ssl workshop JS var collection = schema.getCollection('foods')

MySQL 127.0.0.1:33060+ ssl workshop JS collection.modify("Name_First = 'Fox'").patch({ Name_Middle: 'William' })
Query OK, 1 item affected (0.0020 sec)

Rows matched: 1 Changed: 1 Warnings: 0
MySQL 127.0.0.1:33060+ ssl workshop JS    db.foods.find("Name_First='Fox'")
{
    "_id": "00005da09064000000000027e068",
    "Name_Last": "Mulder",
    "Name_First": "Fox",
    "Name_Middle": "William",
    "favorite_food": [
        {
            "Lunch": "pulled pork sandwich",
            "Dinner": "steak and baked potato",
            "Breakfast": "eggs and bacon"
        }
    ]
}
1 document in set (0.0004 sec)

Importing Large Data Sets

In order to demonstrate indexes, I will need to import a large amount of data. Before I import data, I need to be sure that I have a variable set for the mysqlx client protocol to allow larger client packets. Specifically, I need to set the mysqlx_max_allowed_packet variable to the largest allowable size of 1TB. You can set this variable in the MySQL configuration file (my.cnf or my.ini) and reboot the MySQL instance, or you can set it for your session.

I can check the values of the mysqlx_max_allowed_packet variable from within Shell, and if it isn't set to 1TB, I will modify it for this session. (I can see the value for mysqlx_max_allowed_packet is set to 100MB or 100 megabytes)

MySQL 127.0.0.1:33060+ ssl JS session.runSql('SET @@GLOBAL.mysqlx_max_allowed_packet = 1073741824')
Query OK, 0 rows affected (0.0004 sec)

I don't want to write this large import to the binary log (I have binlog enabled), so I can use the SQL command to SET SQL_LOG_BIN = 0 first. I am going to create a new collection named project, and then import a 400+ megabyte JSON file into the new collection.

MySQL 127.0.0.1:33060+ ssl JS session.runSql('SET SQL_LOG_BIN=0')
Query OK, 0 rows affected (0.0464 sec)
MySQL 127.0.0.1:33060+ ssl JS session.createSchema("project")

MySQL 127.0.0.1:33060+ ssl JS \use project
Default schema `project` accessible through db.
MySQL 127.0.0.1:33060+ ssl JS util.importJson("./workshop/Doc_Store_Demo_File.json", {schema: "project", collection: "GoverningPersons"})
Importing from file "./workshop/Doc_Store_Demo_File.json" to collection `project`.`GoverningPersons` in MySQL Server at 127.0.0.1:33060

.. 2613346.. 2613346
Processed 415.71 MB in 2613346 documents in 2 min 51.0696 sec (15.28K documents/s)
Total successfully imported documents 2613346 (15.28K documents/s)

As you can see from the output above, I imported 2,613,346 documents.
Note: This imported JSON document contains public data regarding businesses in the State of Washington.

Now that I have my 2.6 million documents in the database, I will do a quick search and limit the results to one record by using the limit command. This will show you the keys in the document.

MySQL 127.0.0.1:33060+ ssl JS \use project
Default schema `project` accessible through db.

MySQL 127.0.0.1:33060+ ssl JS    db.GoverningPersons.find().limit(1)
{
    "Ubi": "601544680",
    "Zip": "99205",
    "_id": "00005d6fc3dc000000000027e067",
    "City": "SPOKANE",
    "State": "WA",
    "Title": "GOVERNOR",
    "Address": "RT 5",
    "LastName": "FRISCH",
    "FirstName": "BOB",
    "MiddleName": ""
}
1 document in set (0.0022 sec)

Again – notice an index for the key _id has automatically been added:

MySQL 127.0.0.1:33060+ ssl JS session.runSql('SHOW INDEX FROM project.GoverningPersons')
*************************** 1. row ***************************
Table: GoverningPersons
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: _id
Collation: A
Cardinality: 2481169
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.0137 sec)

To demonstrate indexes on a large dataset, I will perform two searches against all documents in the project collection – where LastName is equal to FRISCH and where LastName is equal to VARNELL. Then, I will create the index on LastName, and re-run the two find queries. Note: I am not displaying all of the returned documents to save space.

MySQL 127.0.0.1:33060+ ssl JS    db.GoverningPersons.find('LastName = "FRISCH"')
{
    "Ubi": "601544680",
    "Zip": "99205",
    "_id": "00005da09064000000000027e069",
    "City": "SPOKANE",
    "State": "WA",
    "Title": "GOVERNOR",
    "Address": "RT 5",
    "LastName": "FRISCH",
    "FirstName": "BOB",
    "MiddleName": ""
}
. . .
82 documents in set (1.3559 sec)

MySQL 127.0.0.1:33060+ ssl JS    db.GoverningPersons.find('LastName = "VARNELL"')
{
    "Ubi": "602268651",
    "Zip": "98166",
    "_id": "00005da09064000000000028ffdc",
    "City": "SEATTLE",
    "State": "WA",
    "Title": "GOVERNOR",
    "Address": "18150 MARINE VIEW DR SW",
    "LastName": "VARNELL",
    "FirstName": "JAMES",
    "MiddleName": ""
}
. . .
33 documents in set (1.0854 sec)

The searches took 1.3559 and 1.0854 seconds, respectively. I can now create an index on LastName. When I create an index, I have to specify the data type for that particular key. And for a text key, I have to specify how many characters of that key I want to include in the index. (Note: see TEXT(20) in the command below)

MySQL 127.0.0.1:33060+ ssl JS db.GoverningPersons.createIndex("i_Name_Last", {fields: [{field: "$.LastName", type: "TEXT(20)"}]})
Query OK, 0 rows affected (8.0653 sec)

Now I will re-run the same two queries where LastName equals FRISCH and where LastName equals VARNELL.

The queries ran much faster with an index – 0.0097 seconds and 0.0008 seconds. Not bad for searching 2.6 million records.

Note: The computer I was using for this post is a Hackintosh, running Mac OS 10.13.6, with an Intel i7-8700K 3.7GHz processor with six cores, with 32GB 2666MHz DDR4 RAM, and an SATA III 6 Gb/s, M.2 2280 SSD. Your performance results may vary.

And I can take a look at the index for LastName:

MySQL 127.0.0.1:33060+ ssl JS    shell.options.set('resultFormat','vertical')
MySQL 127.0.0.1:33060+ ssl JS    session.runSql('SHOW INDEX FROM project.GoverningPersons')
*************************** 1. row ***************************
Table: GoverningPersons
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: _id
Collation: A
Cardinality: 2481169
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: GoverningPersons
Non_unique: 1
Key_name: i_Name_Last
Seq_in_index: 1
Column_name: $ix_t20_F1A785D3F25567CD94716D955607AADB04BB3C0E
Collation: A
Cardinality: 300159
Sub_part: 20
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.0059 sec)
MySQL 127.0.0.1:33060+ ssl JS    shell.options.set('resultFormat','table')

I can create an index on multiple columns as well. Here is an index created on the State and Zip fields.

MySQL 127.0.0.1:33060+ ssl JS db.GoverningPersons.createIndex('i_state_zip', {fields: [ {field: '$.State', type: 'TEXT(2)'}, {field: '$.Zip', type: 'TEXT(10)'}]})
Query OK, 0 rows affected (10.4536 sec)

I can take a look at the index as well (the other index results were removed to save space).

MySQL 127.0.0.1:33060+ ssl JS    shell.options.set('resultFormat','vertical')
MySQL 127.0.0.1:33060+ ssl JS    session.runSql('SHOW INDEX FROM project.GoverningPersons')
. . .
*************************** 3. row ***************************
Table: GoverningPersons
Non_unique: 1
Key_name: i_state_zip
Seq_in_index: 1
Column_name: $ix_t2_00FFBF570DC47A52910DDA38C0C1FB1361F0426A
Collation: A
Cardinality: 864
Sub_part: 2
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 4. row ***************************
Table: GoverningPersons
Non_unique: 1
Key_name: i_state_zip
Seq_in_index: 2
Column_name: $ix_t10_18619E3AC96C74FECCF6B622D9DB0864C2938AB6
Collation: A
Cardinality: 215626
Sub_part: 10
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
4 rows in set (0.0066 sec)
MySQL 127.0.0.1:33060+ ssl JS    shell.options.set('resultFormat','table')

I will run a query looking for the first entry FRISH based upon his state (WA) and Zip (99205). The query result time is still pretty good, even though I am also returning his LastName.

MySQL 127.0.0.1:33060+ ssl JS    db.GoverningPersons.find("State='WA' AND Zip = '99205' AND LastName = 'FRISCH'")
{
    "Ubi": "601544680",
    "Zip": "99205",
    "_id": "00005da09064000000000027e069",
    "City": "SPOKANE",
    "State": "WA",
    "Title": "GOVERNOR",
    "Address": "RT 5",
    "LastName": "FRISCH",
    "FirstName": "BOB",
    "MiddleName": ""
}
1 document in set (0.0011 sec)

To drop an index, use the dropIndex command:

MySQL 127.0.0.1:33060+ ssl JS db.GoverningPersons.dropIndex("i_Name_Last")

NoSQL and SQL in the same database

The great advantage to using MySQL to store JSON documents is that the data is stored in the InnoDB storage engine. So, the NoSQL document store database has features and benefits of InnoDB – such as transactions and ACID-compliance. This also means that you can use MySQL features like replication, group replication, transparent data encryption, etc. And if you need to restore a backup and play back the binlog files for a point-in-time recovery, you can do that as well, as all of the NoSQL transactions are written to the MySQL Binary Log. Here is what a NoSQL transaction looks like in the MySQL binary log:

NoSQL:

collection.modify(""Name_First = 'Selena'").patch({ Name_Middle: 'Kitty' })

MySQL Binlog:

# at 3102
#191018 11:17:41 server id 3451 end_log_pos 3384 CRC32 0xd0c12cca Query thread_id=15 exec_time=0 error_code=0
SET TIMESTAMP=1571411861/*!*/;
UPDATE `workshop`.`foods` SET doc=JSON_SET(JSON_MERGE_PATCH(doc, JSON_OBJECT('Name_Middle','Kitty')),'$._id',JSON_EXTRACT(`doc`,'$._id')) WHERE (JSON_EXTRACT(doc,'$.Name_First') = 'Selena')
/*!*/;
# at 3384
#191018 11:17:41 server id 3451 end_log_pos 3415 CRC32 0xe0eaf4ef Xid = 246
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

Finally, here is an example of how to do a transaction:

MySQL 127.0.0.1:33060+ ssl JS    session.startTransaction()
Query OK, 0 rows affected (0.0013 sec)
MySQL 127.0.0.1:33060+ ssl JS    db.GoverningPersons.modify("Ubi = '601544680'").set("MiddleName", "PETER")
Query OK, 1 item affected (0.0089 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL 127.0.0.1:33060+ ssl JS    session.rollback()
Query OK, 0 rows affected (0.0007 sec)
MySQL 127.0.0.1:33060+ ssl JS    db.GoverningPersons.modify("Ubi = '601544680'").set("MiddleName", "STEVEN")
Query OK, 1 item affected (0.0021 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL 127.0.0.1:33060+ ssl JS    session.commit()
Query OK, 0 rows affected (0.0002 sec)

JSON functions

Since the MySQL Document Store utilizes the MySQL database server and since the documents are stored in InnoDB, you can also use MySQL SQL JSON functions as well to manipulate the data stored in either a JSON document or in a JSON data type. Here is a list of the JSON functions available, and while JSON functions were introduced in 5.7, not all of these functions will be in 5.7 – but they are all in version 8.0.

JSON_ARRAY	JSON_ARRAY_APPEND	JSON_ARRAY_INSERT	JSON_CONTAINS
JSON_CONTAINS_PATH	JSON_DEPTH	JSON_EXTRACT	JSON_INSERT
JSON_KEYS	JSON_LENGTH	JSON_MERGE_PATCH	JSON_MERGE_PRESERVE
JSON_OBJECT	JSON_OVERLAPS	JSON_PRETTY	JSON_QUOTE
JSON_REMOVE	JSON_REPLACE	JSON_SCHEMA_VALID	JSON_SCHEMA_VALIDATION_REPORT
JSON_SEARCH	JSON_SET	JSON_STORAGE_FREE	JSON_STORAGE_SIZE
JSON_TABLE	JSON_TYPE	JSON_UNQUOTE	JSON_VALID
MEMBER OF

But you already use Mongo? And you have MySQL as well?

If you already use Mongo and MySQL, and you want to switch to MySQL, or if your DBA's already know Mongo, then moving to MySQL or using the MySQL doc store is pretty easy. The commands used in Mongo are very similar to the ones used in the MySQL Document Store. The login command is similar to the MySQL “regular” client command in that you specify the user and password with the -u and -p.

In MySQL Shell, you put the username followed by the @ and the IP address. And you can specify which database or schema you want to use upon login. Mongo does have a few shortcuts with the show command, as in show dbs, show schemas or show collections. But you can do almost the same in MySQL Shell by setting variables to equal certain functions or commands (like I did earlier.)

To create a schemda/database in Mongo, you simply execute the use database name command – and it creates the schema/database if it doesn't exist. The other commands having to do with collections are almost the same.

Where there are differences, they are relatively small. Mongo uses the command insert and MySQL uses add when inserting documents. But then, other commands such as the remove document command are the same.

As for the last item in the table below, native Mongo can't run any SQL commands (without using a third-party software GUI tool) – so again, if you have MySQL DBA's on staff, the learning curve can be lower because they can use SQL commands if they don't remember the NoSQL commands.

Command	Mongo	MySQL Document Store (via Shell)
Login	mongo ‐u ‐p ‐‐database	mysqlsh root@127.0.0.1 ‐‐database
Select schema	use database_name	\use database_name
Show schemas/dbs	show dbs	session.getSchemas()
Create schema	use database_name	session.createSchema()
Show collections	show collections or db.getCollectionNames();	db.getCollections()
Create collection	db.createCollection("collectionName");	db.createCollection("collectionName");
Insert document	db.insert({field1: "value", field2: "value"})	db.insert({field1: "value", field2: "value"})
Remove document	db.remove()	db.remove()
Run SQL command		session.runSql(SQL_command)

So, with MySQL, you have a hybrid solution where you can use both SQL and NoSQL at the same time. And, since you are storing the JSON documents inside MySQL, you an also use the MySQL JSON functions to search, update and delete the same records.

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition Visit https://amzn.to/2oPFLI0 for more information.

Filed under JavaScript, MySQL Shell, Python, Uncategorized

MySQL Replication – Creating a New Master/Slave Topology with or without Virtual Machines

February 21, 2013 7 Comments

In my last few posts, I wrote about “How to install MySQL replication using GTID’s” (Part One, Part Two). In this post, I will show you how to install MySQL 5.6 and set up replication between two MySQL servers the “old fashioned way” using the binary log and binary log position.

I am going to create some virtual machines instead of using individual servers. But, you can also use these instructions to create a MySQL replication (master/slave) setup with real servers.

Here is how replication works. On the master server, when there are updates (inserts, updates, deletes, alter, etc.) to the database, MySQL will write the appropriate information to the binlog (binary log), depending upon which replication method you choose.

From: http://dev.mysql.com/doc/refman/5.6/en/binary-log.html

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 16.2, “Replication Implementation“.

Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log“.

The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log. See Section 5.2.3, “The General Query Log“.

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

From: http://dev.mysql.com/doc/refman/5.6/en/replication-formats.html

Replication works because events written to the binary log are read from the master and then processed on the slave. The events are recorded within the binary log in different formats according to the type of event. The different replication formats used correspond to the binary logging format used when the events were recorded in the master’s binary log. The correlation between binary logging formats and the terms used during replication are:

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication (often abbreviated as SBR), which corresponds to the standard statement-based binary logging format. In older versions of MySQL (5.1.4 and earlier), binary logging and replication used this format exclusively.
Row-based binary logging logs changes in individual table rows. When used with MySQL replication, this is known as row-based replication (often abbreviated as RBR). In row-based replication, the master writes events to the binary log that indicate how individual table rows are changed.
The server can change the binary logging format in real time according to the type of event using mixed-format logging. When the mixed format is in effect, statement-based logging is used by default, but automatically switches to row-based logging in particular cases as described later. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication. For more information, see Section 5.2.4.3, “Mixed Binary Logging Format”.

Once a slave has the binlog and binlog position, the slave will connect to the master and retrieve all of the binlog entries from the specified binlog file and after a specified binlog position. The slave creates a thread (IO thread) that connects to the master server. The master server then creates a binlog dump thread, and sends the data to the slave’s IO thread. The slave will in effect retrieve the data that was written to the specified binary log starting after a specific binlog position and then write it to the slave’s relay log (see IO thread states). The slave will then take the data from the relay log and apply it to the slave’s database via the SQL thread. If this isn’t confusing enough, maybe this ~~poorly drawn~~ diagram will help you understand the steps in replication:

For this example, I am starting with a fresh install on one virtual machine running Mac OS X 10.8. MySQL doesn’t have a version specific to 10.8, but I found that the version for 10.7 will work. I am not going to cover how to install MySQL, but I will show you what you need to change in order to make MySQL replication work.

MySQL uses a configuration file (my.cnf or my.ini for Windows) for all of the database variables that will be read by MySQL at startup. After I install MySQL – but before I start MySQL, I will need to choose a copy of the my.cnf file to use, and then modify that file to match my specific use of MySQL. For this example, I am going to just copy the my.cnf file in my MySQL home directory to /etc/my.cnf and then edit the /etc/my.cnf file. You may use the following variables for your my.cnf options file. There are quite a few variables in this file that you will need to change or add. Since the version of the my.cnf file that I copied is mostly blank, here is what I have in my my.cnf file: (And yes, these are not all of the variables that you might need – these are just the basic variables for this example)

[mysqld_safe]
socket = /tmp/mysql.sock

[client]
port=3306

[mysqld]
port = 3306
user = mysql
tmpdir = /tmp
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log_error = /usr/local/mysql/error.log

Since InnoDB is the default storage engine for MySQL (as of 5.5), I want to provide some InnoDB-specific variables:

innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:25M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data

innodb_log_files_in_group = 2
innodb_log_file_size = 25M
innodb_buffer_pool_size = 16M

And then to enable replication, MySQL will need to write all of the select, insert, update and delete etc. statements to the binary log (binlog). I also need to choose the binlog format. I will use “mixed“, which allows MySQL to determine whether or not to use row or statement-based binlog format for each separate SQL statement. The slave will retrieve these statements from the master and then write them to the slave’s relay log before applying these statements to the slave database.

log-bin=mysql-bin
binlog_format=mixed

I need to give each server a unique ID. I always give my master server the id of one (1), and then for the slaves, I will assign a sequential number starting at two for the server-id‘s.

server-id = 1

If you want to possibly use the slave for failover (where you promote the slave to be the master), then you will need to log the updates that are on the slave to the slave’s binary log as well. This will allow any other slaves to use this server as a master. Even though this is a master, if it ever becomes a slave I might want to re-promote it to master at a future date.

log-slave-updates

And in order to enable auto crash recovery on the slaves, enable:

relay-log-recovery

You may now run the installation script for the version of MySQL that you are installing. After you install the new database, you will want to execute the mysql_install_db script. You can also refer to the post-installation procedures on the MySQL web site. Start MySQL, and run the script:

root@macserver01: # ./scripts/mysql_install_db
Installing MySQL system tables...OK

Filling help tables...OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  ./bin/mysqladmin -u root password 'new-password'
  ./bin/mysqladmin -u root -h macserver01 password 'new-password'

Alternatively you can run:

  ./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file ./my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as ./my-new.cnf,
please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

If you ran this script as root, you will need to change the ownership of the mysql-bin and mysql-bin.index files in the mysql data directory to the mysql Unix user.

Now you can start the MySQL server (if it isn’t already started). When you executed the mysql_install_db script, it created the grant tables. You are going to want to change the root password and delete any anonymous accounts. See Securing the Initial MySQL Accounts for specific information for your operating system.

An easy way to change the root password is to use mysqladmin from a command prompt:

$ ./bin/mysqladmin -u root password 'new-password'

Right after you change the root password, you will want to test the new root password by logging in with mysql as root at a Unix prompt:

root@macserver01: $ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2259
Server version: 5.6.9-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Running the mysqladmin program (above) only changed your root password for the localhost.

mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *8B7D321C58724D1990BB8DE02FBD22FE19DB0D0A |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

Now that you have logged in, you can change your password for all of your root accounts:

mysql> UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *8B7D321C58724D1990BB8DE02FBD22FE19DB0D0A |
| 127.0.0.1 | root | *8B7D321C58724D1990BB8DE02FBD22FE19DB0D0A |
| ::1       | root | *8B7D321C58724D1990BB8DE02FBD22FE19DB0D0A |
| localhost |      |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

To find and delete the anonymous accounts, you can find a list of all of the accounts. From a mysql prompt:

mysql> use mysql;
Database changed
mysql> SELECT user, host FROM user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)

The users that are blank are anonymous users. You can double-check the blank users with this statement:

mysql> select user, host from user where user = '';
+------+-----------+
| user | host      |
+------+-----------+
|      | localhost |
+------+-----------+
1 row in set (0.00 sec)

You may now delete the blank users:

mysql> delete from user where user = '';
Query OK, 1 row affected (0.00 sec)

These are the users that are remaining:

mysql> select user, host from user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

Since we are installing MySQL for the first time on your master, you will need to create a replication user for replication. See Creating a User for Replication for more details, but here is a sample replication user creation statement:

mysql> CREATE USER 'replicate'@'%.mydomain.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%.mydomain.com';

mysql> select user, host from user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| replicate | %         |
| root      | 127.0.0.1 |
| root      | ::1       |
| root      | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

You will need to make sure that your replication user matches the domain names or IP addresses, so that it has permissions to access the other server(s).

MySQL should now be up and running on your master server. If you aren’t using VM’s, you may now duplicate these same installation steps on your slave server(s) – but you must change the value of server-id in your my.cnf file for each server – to something other than the value that you have for your master server.

If you are working with virtual machines, you will need to:

Stop the mysqld process
Stop the virtual machine (shutdown the instance)
Copy/duplicate the virtual machine
Change the IP address of the new virtual machine
Change the server-id in the my.cnf file of the new virtual machine
Change my server name for use in file sharing (if file sharing is turned on)
Generate a new UUID for the new slave servers and edit the auto.cnf file. (more on this below)

Beginning with MySQL 5.6, the MySQL server generates a unique ID (UUID) in addition to the server-id supplied by the user. This is available as the global, read-only variable server_uuid. If you aren’t using VM’s, a new UUID will be installed when you install MySQL. On the new VM copies, we will need to generate a new UUID and then edit the auto.cnf file. You can run the select UUID(); command from the master server:

mysql> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 1da4ab9c-7baf-11e2-930f-6a4c3f56f0b5 |
+--------------------------------------+
1 row in set (0.00 sec)

Next, edit the auto.cnf file that is in the MySQL home data directory:

# cat auto.cnf 
[auto]
server-uuid=1da4ab9c-7baf-11e2-930f-6a4c3f56f0b5

Edit the auto.cnf file with a text editor (or use vi) and change the old UUID to the new UUID (example – change 33e3daac-79e5-11e2-9862-ec1bc27a1e29 to 1da4ab9c-7baf-11e2-930f-6a4c3f56f0b5).

After you have installed MySQL on the new slave or copied the original VM and repeated the steps above, you can check to see if your servers have unique server-id‘s and UUID‘s. Login to each instance of mysql:

# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.10-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> show variables where variable_name = 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables where variable_name = 'server_uuid';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 33e3daac-79e5-11e2-9862-ec1bc27a1e29 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

Now that we have our servers up and running, we need to tell our slave server(s) about the master server. If we have already started inserting data into our master servers, we need to put a read lock on the master, show the master status to get the binlog and binlog position of the master, and then release the lock. You may do this on one line separated by a semi-colon to reduce the amount of time that the lock is in place:

mysql> FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 |      540 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

It is important that you save the SHOW MASTER STATUS information, as you will need this for each slave.

Now we need to tell the slave where the master is located, which binlog file to use, and which position to start. Issue this CHANGE MASTER TO command on the slave server(s): (don’t forget to change the values to match your master server)

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='master IP address',
    ->   MASTER_USER='replication user',
    ->   MASTER_PASSWORD='replication user password',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000015',
    ->   MASTER_LOG_POS=540,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.27 sec)

We have two warnings from the above statement. Let’s look at the warnings:

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master.info repository 
is not secure and is therefore not recommended. Please see the MySQL Manual for more 
about this issue and possible alternatives.
2 rows in set (0.00 sec)

The first error “Sending passwords in plain text…” can be ignored. Since we are setting up replication using this method, we have to send the user name and password as plain text. The second error explains that the information in the CHANGE MASTER TO statement is stored in a non-secure file named master.info in your MySQL data directory:

# pwd
/usr/local/mysql/data
# ls -l master.info
-rw-rw----  1 _mysql  wheel  99 Feb 20 15:26 master.info
# cat master.info
23
mysql-bin.000015
540
192.168.1.2
replicate
replicate999
3306
10
....

There are options to not using the master.info file: “MySQL 5.6 extends the replication START SLAVE command to enable DBAs to specify master user and password as part of the replication slave options and to authenticate the account used to connect to the master through an external authentication plugin (user defined or those provided under MySQL Enterprise Edition). With these options the user and password no longer need to be exposed in plain text in the master.info file.” (from https://blogs.oracle.com/MySQL/entry/mysql_5_6_is_a)

For this example, we don’t need to worry about these errors. To begin replication, we need to start the slave:

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

We now can check the status of the slave to see if it is working as a slave, with the SHOW SLAVE STATUS\G command:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.181
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 540
               Relay_Log_File: macos-108-repl02-relay-bin.000002
                Relay_Log_Pos: 1551
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 540
              Relay_Log_Space: 1735
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 33e3daac-79e5-11e2-9862-ec1bc27a1e29
             Master_Info_File: /usr/local/mysql-advanced-5.6.10-osx10.7-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

Two values to note in the slave status shows us that our CHANGE MASTER TO statement worked:

              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 540

We can now execute a statement on the master, to see if it propagates to the slave database. Let’s see what databases are on the master:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

When we execute the same command on the slave, we get the same results. Since we already have a test database, let’s create a table in that database. We can check to see if there are any tables in that database already:

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

Currently there aren’t any tables in the test database. We can now create one on the master database:

mysql> CREATE TABLE `address` (
    ->   `serial_number` int(6) NOT NULL AUTO_INCREMENT,
    ->   `last_name` char(40) NOT NULL DEFAULT '',
    ->   `first_name` char(40) NOT NULL DEFAULT '',
    ->   `address_01` char(40) NOT NULL DEFAULT '',
    ->   `city` char(30) NOT NULL DEFAULT '',
    ->   `state` char(20) NOT NULL DEFAULT '',
    ->   `zip` char(11) NOT NULL DEFAULT '',
    ->   `phone` char(15) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`serial_number`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| address        |
+----------------+
1 row in set (0.00 sec)

And let’s take a look at the new master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 |     1808 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

So, if we have replication set up correctly, when we go to the slave, we should see this table on the slave as well. Let’s execute the same same show table statement on the slave:

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| address        |
+----------------+
1 row in set (0.00 sec)

Replication is now up and running. You may continue these steps for additional slaves that you install manually or slaves where you copy the VM.

I can also use the MySQL Utility script mysqldbcompare to see if both tables are the same. I wrote about mysqldbcompare in an earlier post. Just like this post, on the master and slave databases, I will create a user named “scripts” to execute the mysqldbcompare script: I don’t need to actually create the user on the slave, as when I execute this command on the master, it will be replicated over to the slave.

mysql> CREATE USER 'scripts'@'%' IDENTIFIED BY 'scripts999';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'scripts'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

I can now run the mysqldbcompare script:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.181 --server2=scripts:scripts999@192.168.1.182 test:test --run-all-tests --changes-for=server2 --difftype=sql
# server1 on 192.168.1.181: ... connected.
# server2 on 192.168.1.182: ... connected.
# Checking databases test on server1 and test on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     address                                 pass    pass    pass   

Databases are consistent.
#
# ...done

The SQL statement that I executed on the master to create the table “address” has been replicated on the slave, so replication is running and confirmed to be working.

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, MySQL Replication, Python, Scripting, Uncategorized

Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication

January 31, 2013 7 Comments

In my last two posts, I wrote about setting up replication with MySQL 5.6 using Global Transaction Identifiers. Even when I set up replication “the old-fashioned way“, one thought always enters my mind – did all of the data copy over to the slave? And, even after the master/slave has been running for a while, I am always wondering if the data in the slave matches the master. Or did the change that I made to that table make it over to the slave? It is probably more of a case of paranoia on my part, as MySQL replication is very reliable and works really well.

A few months ago, I started writing about the MySQL Utilities. If you haven’t heard about the MySQL Utilities:

“MySQL Utilities is a package of utilities that are used for maintenance and administration of MySQL servers. These utilities encapsulate a set of primitive commands, and bundles them so they can be used to perform macro operations with a single command. MySQL Utilities may be installed via MySQL Workbench, or as a standalone package. The utilities are written in Python, available under the GPLv2 license, and are extendable using the supplied library. They are designed to work with Python 2.x greater than 2.6.” (from the introduction to MySQL Utilities page)

In order to reduce my paranoia (I will never be able to eliminate it), I can simply use the mysqldbcompare utility. The mysqldbcompare utility “compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.” (from: mysqldbcompare — Compare Two Databases and Identify Differences)

You don’t have to use mysqldcompare to compare two databases in replication. You may compare any two databases, even if they are on the same server. But for this post, I will be comparing two databases on two separate servers in a master/slave replication topology.

The utility is fairly easy to use. You just identify the two servers and which databases you want to compare. You will need to refer to my earlier post on using the MySQL Utilities for more information on how to execute the scripts.

I will be comparing a database that is on my master server (at 192.168.1.2) and the same database that is on one of the slaves (at 192.168.1.122) connected to that master. Instead of using root to execute the scripts, I create and use a MySQL user named “scripts” to use when I run a script. The syntax for mysqldbcompare is fairly easy, and you can refer to the mysqldbcompare man page for more of the commands and their usage:

I need to specify the servers, user name and passwords:

--server1=scripts:scripts999@192.168.1.2 
--server2=scripts:scripts999@192.168.1.122

The name of the databases to compare (database_server1:database_server2):

cbgc:cbgc

Do not stop the script at the first difference that is found. Process all objects.

--run-all-tests

Specify the server to show transformations to match the other server.

--changes-for=server2

Specify the difference display format. Permitted format values are unified, context, differ, and sql. The default is unified.

--difftype=sql

Now that I have decided on which options to use, I can run the scripts from within the MySQL Workbench Utilities shell:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2 \
  --server2=scripts:scripts999@192.168.1.122 \
  cbgc:cbgc --run-all-tests --changes-for=server2 --difftype=sql
# server1 on 192.168.1.2: ... connected.
# server2 on 192.168.1.122: ... connected.
# Checking databases cbgc on server1 and cbgc on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Activity_Affiliate                      pass    pass    pass    
# TABLE     FedEx_2nd_Day                           pass    pass    pass    
# TABLE     FedEx_Express_Saver                     pass    pass    pass    
# TABLE     FedEx_Ground                            pass    pass    pass    
# TABLE     FedEx_Home                              pass    pass    pass    
# TABLE     FedEx_Priority_Overnight                pass    pass    pass    
# TABLE     FedEx_Standard_Overnight                pass    pass    pass    
# TABLE     Orders                                  pass    pass    pass    
# TABLE     USPS                                    pass    pass    pass    
# TABLE     activity                                pass    pass    pass    
# TABLE     comics                                  pass    pass    pass    
# TABLE     coupons                                 pass    pass    pass    
# TABLE     customer                                pass    pass    pass    
# TABLE     giftcert                                pass    pass    pass   

Databases are consistent.
#
# ...done

The output shows that my databases are consistent. I have a rather small database, and I re-executed the script again with the “time” command, and here are the time results:

real	0m4.519s
user	0m0.429s
sys	0m0.068s

It took about 4.5 seconds to execute on my database which is about 25 megabytes in size. Obviously, the time will increase relative to the complexity and size of your database. And the time will increase relative to the number of differences that the script finds.

In order to show you what happens when the databases are not in sync, I will now stop the slave database (at 192.168.1.122), and then add a line to one of the tables in the master database (at 192.168.1.2), and re-run the script (while the slave is still stopped).

On the slave server:

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

On the master server:

mysql> insert into `cbgc`.`activity` ( `Customer_Serial_Number`, `Customer_Activity_Action`) \
          values ( '1201201', 'Test Visit');
Query OK, 1 row affected (0.96 sec)

Now I can run the mysqldbcompare script again. The slave is still turned off:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2 \
  --server2=scripts:scripts999@192.168.1.122 cbgc:cbgc \ 
  --run-all-tests --changes-for=server2 --difftype=sql
# server1 on 192.168.1.2: ... connected.
# server2 on 192.168.1.122: ... connected.
# Checking databases cbgc on server1 and cbgc on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Activity_Affiliate                      pass    pass    pass    
# TABLE     FedEx_2nd_Day                           pass    pass    pass    
# TABLE     FedEx_Express_Saver                     pass    pass    pass    
# TABLE     FedEx_Ground                            pass    pass    pass    
# TABLE     FedEx_Home                              pass    pass    pass    
# TABLE     FedEx_Priority_Overnight                pass    pass    pass    
# TABLE     FedEx_Standard_Overnight                pass    pass    pass    
# TABLE     Orders                                  pass    pass    pass    
# TABLE     USPS                                    pass    pass    pass    
# TABLE     activity                                FAIL    FAIL    FAIL    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE cbgc.activity 
  DROP PRIMARY KEY, 
  ADD PRIMARY KEY(serial_id), 
AUTO_INCREMENT=7542;

# Row counts are not the same among cbgc.activity and cbgc.activity.
#
# Transformation for --changes-for=server2:
#

INSERT INTO cbgc.activity (serial_id, Customer_Serial_Number, Customer_Activity_Action, 
Customer_Activity_Date_Time, Customer_Activity_Info, Notes, HTTP_REFERER) 
VALUES('7541', '1201201', 'Test Visit', NULL, NULL, NULL, NULL);


# TABLE     comics                                  pass    pass    pass    
# TABLE     coupons                                 pass    pass    pass    
# TABLE     customer                                pass    pass    pass    
# TABLE     giftcert                                pass    pass    pass   

# Database consistency check failed.
#
# ...done

The script alerted me to two issues. It tells me that I have one row of data missing (from the insert statement), but it also notices that my auto-increment on the activity database needs to be updated as well. If I simply run the insert statement, my auto-increment value will be incorrect. So, I need to run the auto-increment change first, and then I can execute the insert statement.

There are other choices of output that you can use besides sql – unified, context and differ. You can try each one and see which one will fit your needs. I prefer the sql output.

Since I stopped the slave, I can just start it again and it will catch up with the master. But, if you are working with an active master, you might want to put a read lock on the database, run the script again, and then make your changes. This is especially true if the changes involve something dynamic like a primary key or auto-increment column, as those values could change while you are trying to run the statements.

Let’s see what happens when we someone else makes some changes and we aren’t aware of the changes. But first, I will start the slave so it can catch up to the master (for the earlier missing statements). We will then stop the slave and let someone else make a few changes. And then we can run the mysqldbcompare utility again:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2 \
  --server2=scripts:scripts999@192.168.1.122 cbgc:cbgc \
  --run-all-tests --changes-for=server2 --difftype=sql
# server1 on 192.168.1.2: ... connected.
# server2 on 192.168.1.122: ... connected.
# Checking databases cbgc on server1 and cbgc on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Activity_Affiliate                      pass    pass    pass    
# TABLE     FedEx_2nd_Day                           pass    pass    pass    
# TABLE     FedEx_Express_Saver                     pass    pass    pass    
# TABLE     FedEx_Ground                            pass    pass    pass    
# TABLE     FedEx_Home                              pass    pass    pass    
# TABLE     FedEx_Priority_Overnight                pass    pass    pass    
# TABLE     FedEx_Standard_Overnight                pass    pass    pass    
# TABLE     Orders                                  pass    pass    pass    
# TABLE     USPS                                    pass    pass    pass    
# TABLE     activity                                FAIL    pass    pass    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE cbgc.activity 
  DROP PRIMARY KEY, 
  DROP COLUMN Dummy_Field, 
  ADD PRIMARY KEY(serial_id);


# TABLE     comics                                  pass    pass    pass    
# TABLE     coupons                                 FAIL    pass    pass    
#
# Transformation for --changes-for=server2:
#

ALTER TABLE cbgc.coupons 
  CHANGE COLUMN Coupon_Notes Coupon_Notes varchar(100) NULL;


# TABLE     customer                                pass    pass    pass    
# TABLE     giftcert                                pass    pass    pass   

Databases are consistent.
#
# ...done

This time, the script took about 20 seconds to run:

real	0m20.058s
user	0m0.452s
sys	0m0.224s

We can see from the output that a column named Dummy_Field was dropped. We can also see that the Coupon_Notes column has changed (or is different on the master). I can now take these changes and implement them on the slave (again assuming that these changes aren’t waiting to be sent to the slave). In my case, once I start the slave, the changes will propagate over to the slave, but there may be cases where that transaction was lost or skipped on the slave (for example, if you had to do a SET GLOBAL sql_slave_skip_counter = N on the slave). You now have the ability to easily make the slave the same as the master by executing these differences. If the changes are too complicated, then you might want to look at re-creating your slave.

There are many different options and output possibilities, so I would encourage you to just try mysqldbcompare and see how it works for you. And as always, feel free to post your feedback in the comments section.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, MySQL Replication, Python, Scripting, Uncategorized

Using the MySQL Script mysqlfailover for Automatic Failover with MySQL 5.6 GTID Replication

December 6, 2012 10 Comments

This post is the second in a series that I will be doing on MySQL Workbench Utilities – Administer MySQL with Python Scripts. You may want to read the first half of this post to understand how MySQL Workbench Utilities work and how you access the scripts. These scripts were written by Chuck Bell (a MySQL employee) and are available as stand-alone scripts (see Chuck’s blog for more information) or as part of the MySQL Workbench utility.

I am going to show you one way that you can use the mysqlfailover script to monitor your replication stack and automatically failover to a slave database when your master has failed. You will need to have both your master and slave databases running with GTID’s enabled. I will provide a brief overview of GTID’s, and how to start replication with GTID enabled. The term “automatically failover” in the title might be a bit misleading, as the failover process is automatic, but it does take a couple of minutes. It is automatic but not instantaneous. Also, you may use the mysqlfailover script on a master with multiple slaves, but in this example I will only have one master and one slave.

Let’s start with a quick review of GTID’s – or global transaction identifiers. GTID’s were introduced in MySQL 5.6.5. With GTID’s, each transaction can be identified and tracked as it is committed on the originating server and applied by any slaves; this means that it is not necessary when using GTIDs to refer to log files or positions within those files when starting a new slave or failing over to a new master, which greatly simplifies these tasks.

(From http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html)

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction when it is committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs.

The GTID has this format: GTID = source_id:transaction_id – with the source_id identifying the originating server (in this case, the master server), and the transaction_id being a sequential number of the transactions that were committed on the originating server. For example, the twenty-third (23rd) transaction to be committed originally on the server having the UUID 3E11FA47-71CA-11E1-9E33-C80AA9429562 has this GTID:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

(From http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html.)

When you provide your slave server(s) with the information about which master to use for replication, without using GTID’s, you would normally execute a statement like this on the slave:

CHANGE MASTER TO
  MASTER_HOST = '192.168.1.121',
  MASTER_USER = 'replicate',
  MASTER_PASSWORD = 'password',
  MASTER_PORT = 3306,
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS = 150691098,
  MASTER_CONNECT_RETRY = 10;

When you have GTID’s enabled, you don’t have to provide the log file and position, you only have to provide this:

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.121',
MASTER_PORT = 3306,
MASTER_USER = 'replicate',
MASTER_PASSWORD = 'password',
MASTER_AUTO_POSITION = 1;

If you have worked with replication before, this should make some sense. If not, then you will probably want to read more about replication and GTID’s.

For this example, I am going to take an existing MySQL database, export the data, install the latest version of MySQL (in this case 5.6.8), enable GTID’s, and then demo the mysqlfailover script. This post is going to be a long one, but I will not try to go into as much detail as I normally would. I am writing this after I have already tested this – so I am writing from memory – and hopefully I won’t forget any steps. Here is my current configuration:

I have an application and web server at 192.168.1.2, a MySQL master server at 192.168.1.121 and a MySQL slave server at 192.168.1.122.

For my master and slave servers, I was running MySQL version 5.5.27. To export the data, I am going to just use mysqldump – but I will not export any of the MySQL tables (such as information_schema, mysql, performance_schema and test). When I upgrade from a new major version of MySQL (such as from 5.5. to 5.6), I like to start with a new install versus trying to upgrade from a previous version. (For large databases, this might not be as efficient or even possible, but since my database dump is only 26 megabytes, this will work for me.)

Since I only have a few MySQL users, I keep the SQL statements that I need to re-create these users and their permissions in a text file. Obviously this isn’t the best and most secure way to do this, but this is for my home system, so it doesn’t matter in my case. If you prefer, you can just upgrade from 5.5 to 5.6 and not export the data – and instructions for upgrading this way may be found via this link – Installing and Upgrading MySQL.

You need to make sure that there aren’t any updates to the database while you are doing your mysqldump. You can lock the database with this command from a mysql prompt FLUSH TABLES WITH READ LOCK; and then unlock it with UNLOCK TABLES;. Here is the mysqldump command that I used:

/usr/local/mysql/bin/mysqldump --databases [list of your databases here, separated by spaces] 
--add-drop-database --add-drop-table --user=root --pass= > /users/tonydarnell/2012_11_30_1645_dbdump.db

(Yes, you will get a notification “Warning: Using a password on the command line interface can be insecure.” so you could leave the password blank and enter it when prompted.)

I usually also create a backup of the entire database in case I have any problems or if I destroy something.

/usr/local/mysql/bin/mysqldump --all-databases --add-drop-database --add-drop-table --user=root \
  --pass=[your_password]
 > /users/tonydarnell/2012_11_30_1645_all_dbdump.db

I keep my data directory on two external USB hard drives that I have set up as a RAID on each machine. I use a symbolic link from my MySQL data directory (/usr/local/mysql/data) to point to a directory on the RAID – with this command ln -s /volumes/server_raid/mysql_data/data /usr/local/mysql/data). Since I am creating a new install, I just rename the directory on the raid mv /volumes/server_raid/mysql_data/data /volumes/server_raid/mysql_data/data-old. When I install MySQ it will create a new data directory. I then can move the new data directory to the RAID, and recreate the link. You could also use this method to move your data directory to another internal or SSD drive.

Now I install MySQL version 5.6.8. (see http://dev.mysql.com/doc/refman/5.6/en/installing.html for instructions on installing MySQL.)

Once I have MySQL 5.6.8 installed on the master (including running any post-install scripts per the instructions above), I can import my database.

mysql -uroot -p < /users/tonydarnell/2012_11_30_1645_all_dbdump.db

I then start MySQL, login, create my users, and the master is finished and ready. I then repeat the same procedures on a slave machine. Since no one has updated the master since my data dump, the master and the slave should be exact copies of each other. If you are using virtual machines, once you have created the first virtual machine to be used as your master, you can just duplicate the VM to be your slave machine. Just be sure to change the server-id option in your mysql config file (my.cnf or my.ini) to be a different number.

It is time to turn on GTID's and to get replication started. If you already have a master and slave configured, then you can refer to this link on how to start replication using GTID's http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html.

You can either start the GTID process on both MySQL servers by adding these options when you start mysqld:

--gtid_mode=ON --log-bin --log-slave-updates --disable-gtid-unsafe-statements

Or, you can add these options to your MySQL config file (/etc/my.cnf or c:\my.cnf or c:\Windows\my.ini).

gtid_mode=ON
disable-gtid-unsafe-statements = 1
log-bin
log-slave-updates

Binary logging should be enabled on the master, and you will also want to enable binary logging on the slave, so when the slave is promoted to the master, you can make the old master a slave to the new master. See this link for binary log options and variables.

(Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency)

Now that you have both of these options in place, you may start both of your servers. On the slave, you will want to add –skip-slave-start to the mysqld command. You will want to start the slave manually, after you have given the slave the information about the master from a mysql prompt:

To test and make sure that GTID is running, you may issue this command on both servers:

mysql> show global variables like '%GTID%';
+--------------------------------+--------------------------------------------+
| Variable_name                  | Value                                      |
+--------------------------------+--------------------------------------------+
| disable_gtid_unsafe_statements | ON                                         |
| gtid_done                      | 6CD03F68-3B38-11E2-99FA-588CB3DE3E9D:1-242 |
| gtid_lost                      |                                            |
| gtid_mode                      | ON                                         |
| gtid_owned                     |                                            |
+--------------------------------+--------------------------------------------+
5 rows in set (0.00 sec)

You can see the value for gtid_done contains the GTID information – source_id:transaction_id, where 1-242 is the range of transactions that have been committed. (Your values will be different)

Now that we have MySQL replication running with GTID’s enabled, we can look at running the mysqlfailover script. I use Perl on my web site, and my Perl scripts make a connection to the MySQL database by reading the connection information from a text file (connection file) stored on the web server in the CGI directory. (for more information on how I use this connection file, please see Connecting to MySQL with Perl)

This connection file contains the database name, IP address, mysql user and password. This file determines which MySQL server will be used by the web server and in this example the file is named accessWEB. The file contains the following: (you will have to configure the file to match your system)

scripts_db
192.168.1.121
user_name
password

With the mysqlfailover script, you have the option to run a script before failover and after failover. There is an option to also run a script prior to failing over, and one to run a script after failover has finished and mysqlfailover has refreshed the health report.

For this test, I will create a script that will change the connection file information to point to the slave database when the master fails. This is as simple as creating a new connection file with the slave’s information, and then copying it on top of the existing file. I will create a file for each server, and name the files after their IP addresses. So, the file 192-168-1-121.txt will have the same information as the current accessWEB connection file, and the file 192-168-1-122.txt will contain:

scripts_db
192.168.1.122
user_name
password

For my pre-failover script, I will then create a shell script that input some text into a file so that I can see when failover started. The script will be named “prefail.sh”, and it will contain the following:

cd /Library/WebServer/cgi-bin/
echo "failover started" > failover_started.txt

I will need to make sure that prefail.sh has execute privileges and that all of the connection files have the correct privileges as well. And I would want to test the script prior to using it.

For my post-failover script, I will create a shell script that will send me a text message, will change the connection file after failover has occurred and input some text into a file so that I can see when failover finished. I will name this script postfail.sh. It will contain the following:

cd /Library/WebServer/cgi-bin/
cp 192-168-1-122.txt accessWEB
echo "Failover has occurred." | mail 4045552232@messaging.att.net
echo "failover finished" > failover_finished.txt

I have my master and slave using GTID, and the web server is connecting to the master (192.168.1.121). I can now run the mysqlfailover script. I don’t want to run it on the master or slave, because if one of them fails, then the script could fail as well. I will run the script on the web server. If it fails, then it doesn’t matter if the MySQL servers are down, as no one can access the web site anyway.

Prior to running this script, I created a MySQL user name “scripts” to use for the mysqlfailover script. I gave the user the same permissions as root. I have a few options that I will use when executing the mysqlfailover script:

--master=scripts:scripts123@192.168.1.121:3306 - connection information for the master
--slaves=scripts:scripts123@192.168.1.122:3306 - connection information for the slave(s)
--candidates=scripts:scripts123@192.168.1.122:3306 - a list of candidates for failover
--exec-before=/users/tonydarnell/scripts/prefail.sh - the script to execute before the failover
--exec-after=/users/tonydarnell/scripts/postfail.sh - the script to execute after the failover

There is an option for setting the refresh time for the script with the –interval=X option (where X is the number of seconds for the interval), but I will be using the default of 15 seconds.

I am also adding the –force option – because at startup, the console will attempt to register itself with the master. If another console is already registered, and the failover mode is auto or elect, the console will be blocked from running failover. When a console quits, it deregisters itself from the master. If this process is broken, the user may override the registration check by using the –force option.

(From: http://dev.mysql.com/doc/workbench/en/mysqlfailover.html

I can then open a terminal window and run the mysqlfailover script:

# mysqlfailover --master=scripts:scripts123@192.168.1.121:3306 --slaves=scripts:scripts123@192.168.1.122:3306 
--candidates=scripts:scripts123@192.168.1.122:3306 --exec-before=/users/tonydarnell/scripts/prefail.sh 
--exec-after=/users/tonydarnell/scripts/postfail.sh --force

Here is a screen shot of the script in action:

To test the script, and to simulate the master server crashing or the mysqld process failing, I will just kill the mysqld process that is on the master server. Since I am using mysqld_safe to start the mysqld process, I will need to kill that process as well.

Once the mysqld processes have been killed, and the mysqlfailover script has refreshed (or you can refresh it manually), the failover process will start. This entire process might take 20-30 seconds (give or take), and you will see something similar to this:

Once the process has completed, the mysqlfailover script will now show you that the failover process has completed and the slave at 192.168.1.122 is now the master.

If you have more than one slave attached to the master, there are options that will allow you to specify a slave to become the master, or you can have the mysqlfailover script decide which slave is the best candidate to be promoted to master. You will need to refer to the mysqlfailover page for more information.

We can check to make sure that our scripts ran successfully by checking the actions of our pre and post-failover scripts. We can check to see if the files were created by our “echo” commands in both scripts:

We can also check our accessWEB file, to see that it has the new connection information.

With the mysqlfailover script, both of our pre and post-failover scripts were executed, and our slave was promoted to the master. Even though the failover process wasn’t immediate (the entire failover process took about a minute), it was successful.

Once the failover has completed, and the old master has been restarted, you can then make the old master (192.168.1.121) a slave to the new master with this command:

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.122',
MASTER_PORT = 3306,
MASTER_USER = 'replicate',
MASTER_PASSWORD = '',
MASTER_AUTO_POSITION = 1;

The mysqlfailover script will recognize the new slave, but now your scripts will not be correct in that it will not copy the master info to the accessWEB file – so you would want to change them to match the new configuration. Of course, you can obviously create scripts that provide the logic to failover to whichever server is available – maybe that is a topic for a future post.

If you prefer to have the old master as the current master, then you can wait until the old master catches up to the new master, stop both servers, and make the old master the new master again. But it is easier to just keep both servers in the new configuration until failover happens again. You could also use the mysqlfailover script to fail over to old master as well – making it the new master again.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, Python, Scripting, Uncategorized

MySQL Workbench Utilities – Clone MySQL users with mysqluserclone

August 19, 2012 3 Comments

This post is one in a series that I will be doing on MySQL Workbench Utilities – Administer MySQL with Python Scripts.

MySQL Utilities are a part MySQL Workbench. The utilities are written in Python, available under the GPLv2 license, and are extendable using the supplied library. They are designed to work with Python 2.x greater than 2.6. If you don’t have Workbench, you may download the MySQL Utility scripts from launchpad.net. You will also need to install Python and to make sure that your execution $PATH’s are set correctly.

—————————————–

I recently created a new MySQL replication slave instance on a new server, and I needed a way to copy a few of the users from the master database over to the slave database. With the mysqluserclone script, it was fairly easy. The user that I wanted to copy from the master to the slave was named “WebUser”.

Normally, to duplicate a user, I start by taking a look at the privileges that this user has by issuing a “SHOW GRANTS” statement, like this:


mysql> SHOW GRANTS FOR WebUser;
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for WebUser@%                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%' IDENTIFIED BY PASSWORD '*xxxxxxxxxx' |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

I would then go to the new instance, copy the above SQL statement, and issue the CREATE USER command using the HASH value of the password, and then issue the same “GRANT SELECT,…” statement – but without the IDENTIFIED BY PASSWORD part of the command.


mysql> CREATE USER 'WebUser'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS for WebUser;
+----------------------------------------------------------------------------------+
| Grants for WebUser@%                                                            |
+----------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%' |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

This doesn’t take that much time, but what if you wanted to create the same user on several remote machines? You would have to connect to each machine, login to mysql, and then issue the commands. With mysqluserclone, the process is much easier and faster.

From the mysqluserclone man page: “This utility [mysqluserclone] uses an existing MySQL user account on one server as a template, and clones it to create one or more new user accounts with the same privileges as the original user. The new users can be created on the original server or a different server.”

As the man page states, you may clone an existing user on the local machine, or on any remote machine. In my case, I wanted to clone a user to a remote machine. You need to make sure that the MySQL user on each machine that you are using to perform the user creation has the proper MySQL permissions on that machine. I have created a user named “utility” on all of my servers, which I use instead of the root user for creating users and executing scripts.

I needed to clone the user named “WebUser” from my master server (192.168.1.2) to my new replication slave server (192.168.1.5). I issued the following command, and this is what happened:


$ mysqluserclone --source=utility:tonyd765@192.168.1.2:3306 --destination=utility:tonyd959@192.168.1.5:3306 \
  WebUser@localhost WebUser:secret1@localhost
# Source on 192.168.1.2: ... connected.
# Destination on 192.168.1.5: ... connected.
ERROR: User WebUser:secret1@localhost already exists. Use --force to drop and recreate user.

At first, it appeared that somehow during the creation of the new replication slave, I had already created the user named WebUser, or that user already existed. I then realized that I had put in the wrong IP address of my new server. In this situation, it was nice that mysqluserclone checked to make sure that the user did not exist before attempting to clone that user. After getting the correct IP address (192.168.1.3), I decided that I wanted to clone the original WebUser (from the master server) to two new users on the new slave server – named WebUser1 and WebUser2.


$ mysqluserclone --source=utility:tonyd32s@192.168.1.2:3306 --destination=utility:tonyd32s@192.168.1.3:3306 \
 WebUser@localhost WebUser1:secret1@localhost WebUser2:secret2@localhost
# Source on 192.168.1.2: ... connected.
# Destination on 192.168.1.3: ... connected.
# Cloning 2 users...
# Cloning WebUser@localhost to user WebUser1:secret1@localhost 
# Cloning WebUser@localhost to user WebUser2:secret2@localhost 
# ...done.

In the above example, the first user “WebUser@localhost” was the user to be cloned, while WebUser1 and WebUser2 are the names of the new users on the remote machine.

I saved the syntax in a shell script, so that the next time I need to clone a user (or users), I can just quickly edit the script and execute it. This makes it a lot easier than having to do everything manually. There are a few more options that you may use with mysqluserclone, and you should reference the official mysqluserclone man page for more information.

Option	Description
–help	Display a help message and exit.
–destination=	Connection information for the destination server in [:]@[:][:] format.
–dump, -d	Display the GRANT statements to create the account rather than executing them. In this case, the utility does not connect to the destination server and no –destination option is needed.
–format=, -f	Specify the user display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. This option is valid only if –list is given.
–force	Drop the new user account if it exists before creating the new account. Without this option, it is an error to try to create an account that already exists.
–include-global-privileges	Include privileges that match base_user@% as well as base_user@host.
–list	List all users on the source server. With this option, a destination server need not be specified.
–quiet, -q	Turn off all messages for quiet execution.
–source=	Connection information for the source server in [:]@[:][:] format.
–verbose, -v	Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.
–version	Display version information and exit.

Source: http://dev.mysql.com/doc/workbench/en/mysqluserclone.html

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, Python, Scripting

MySQL Workbench Utilities – Administer MySQL with Python Scripts

August 19, 2012 Leave a comment

Over the next few months, I am going to be writing about the MySQL Utilities, and I will be posting links to each individual blog on this page.

If you haven’t heard of the MySQL Utilities (from the introduction to MySQL Utilities page):

If you don’t have Workbench, you may download the MySQL Utility scripts from launchpad.net. You will also need to install Python and to make sure that your execution $PATH’s are set correctly.

The MySQL Utilities are maintained by Chuck Bell. You may find more information about MySQL Utilities on his web site..

To start the MySQL Utilities, from within MySQL Workbench, simply click on the “MySQL Utilities” icon located in the top right area of the home window.

Or, from the MySQL Workbench Plugins menu, select “Start Shell for MySQL Utilities”.

When you open MySQL Utilities, you should be taken to a terminal window, with a list of all of the utilities that are available.

As long as you have your $PATH set correctly, you can just run the scripts from any terminal window and in cron jobs.

Here is a list of the available utilities, with a link to the manual page and a link to my blog about that page (if I have written a post about that utility).

Blog Post Link	Script Name & Man Page	Description
	MySQL Utilities Overview	Brief overview of command-line utilities
	mut	MySQL Utilities Testing
	mysqldbcompare	Compare Two Databases and Identify Differences
	mysqldbcopy	Copy Database Objects Between Servers
	mysqldbexport	Export Object Definitions or Data from a Database
	mysqldbimport	Import Object Definitions or Data into a Database
	mysqldiff	Identify Differences Among Database Objects
	mysqldiskusage	Show Database Disk Usage
Blog	mysqlfailover	Automatic replication health monitoring and failover
	mysqlindexcheck	Identify Potentially Redundant Table Indexes
	mysqlmetagrep	Search Database Object Definitions
	mysqlprocgrep	Search Server Process Lists
	mysqlreplicate	Set Up and Start Replication Between Two Servers
	mysqlrpladmin	Administration utility for MySQL replication
	mysqlrplcheck	Check Replication Prerequisites
	mysqlrplshow	Show Slaves for Master Server
	mysqlserverclone	Clone Existing Server to Create New Server
	mysqlserverinfo	Display Common Diagnostic Information from a Server
Blog	mysqluserclone	Clone Existing User to Create New User

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, Python, Scripting

Using Java to Connect to MySQL Enterprise Monitor with Plugin for Connector/J

October 14, 2011 Leave a comment

If you use MySQL for any number of databases, and if you are not using the MySQL Enterprise Monitor (MEM) to manage your databases, then you are missing out on a great DBA tool.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-introduction.html:

“MySQL Enterprise Monitor is a companion product for MySQL Server that monitors your MySQL instances, notifies you of potential issues and problems, and advises you how to fix the issues. MySQL Enterprise Monitor can monitor all kinds of configurations, from a single MySQL instance that is important to your business, all the way up to a huge farm of database server machines powering a busy web site. “

The query analyzer function of the Enterprise Monitor helps you to analyze/tune your queries. It can tell you which queries are being run the most often, which ones are taking the longest to run, it can even help you find queries that are returning a zero result set, and other query “violators”.

But the caveat is that in order to use the query analyzer function, you have to redirect your MySQL traffic through a proxy server (configured during the MEM installation). Using the proxy server is an additional step that might be acceptible for testing on development servers, but it might not be a great idea in a production environment.

However, if you are using Java for your database connections, you can bypass the proxy server and send your query information directly to the MEM Service Manager. This is accomplished with the MySQL Enterprise Plugin for Connector/J.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-qanal-using-cj.html:

“The MySQL Enterprise Plugin for Connector/J lets you use the Query Analyzer to monitor MySQL queries from any application using the Connector/J JDBC driver. As described in Section 3.2, “Query Analyzer User Interface“, the Query Analyzer can help you locate queries that are inefficient or slow. Tuning such queries helps to shorten load times for web pages, and improves overall system responsiveness and scalability.”

As stated, the information about the queries is sent directly to the MySQL Enterprise Service Manager. Once you install the MySQL Enterprise Plugin for Connector/J and tweak your Java database connection string, query analysis becomes available for your Java applications. And, the Enterprise Monitor can show you the location of the “offensive” Java code. Here is an example, with the code location highlighted in blue:

There are a few things that you need to do before you can use this plugin. The prerequisites are:

– MySQL Connector/J version 5.1.12 or later.
– JDK-1.5.0 or later.
– MySQL Enterprise Service Manager version 2.2 or later.
– The MySQL instance that the application uses must be monitored by a MySQL Enterprise monitoring agent. The mysql.inventory table must be accessible to the configured user for the Connector/J connection to get the UUID of the monitored server.
– Apache Commons logging in the CLASSPATH of the application being analyzed. If you are not already using Commons Logging, modify the application’s CLASSPATH as described in the following section to point to the JAR file bundled with the MySQL Enterprise Monitor product.

I don’t write Java code, but I wanted to be able to test this functionality. I found an example script on MySQL’s web site, and so I was able to modify it to send a test query to my installation of MEM.

I did run into one problem – I wasn’t setting my CLASSPATH variable correctly. To solve this problem, I edited my .profile file for the root user, and added the following JAR file locations to my CLASSPATH: (the colors are added for clarity)



export set CLASSPATH=/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/mysql-connector-java-commercial-5.1.17-bin.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/c-java-mysql-enterprise-plugin-1.0.0.61.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/required/commons-logging-1.1.1.jar:

I am running MySQL on a Mac Pro Server, so you will need to modify the file paths in your CLASSPATH to match your system.

After you have completed the prerequisites, you are ready to see if you can connect to the Service Manager with a query via Java. Here is a sample script that you can use (you will need to modify it your system parameters):



import java.io.IOException;

import java.sql.*;
// Notice, do not import com.mysql.jdbc.*

// or you will have problems!
public class LoadDriver {

   public static void main(String[] args) {
         try {

            // The newInstance() call is a work around for some

            // broken Java implementations
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.1.2/database_name?"+

               "user=root&password=root_password"+

               "&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters"+

               "&serviceManagerUrl=http://192.168.1.2:18080/"+

               "&serviceManagerUser=agent"+

               "&serviceManagerPassword=agent_password");
            //Get a Statement object

               Statement stmt = conn.createStatement();

               ResultSet rs = stmt.executeQuery("SELECT sleep(120)");

               System.out.println("Closing connection");

conn.close(); } catch (Exception ex) { // handle the error ex.printStackTrace(); } } }

You will need to compile the script before running it. Here are the results from compiling and then executing the script: (since you are doing a SELECT SLEEP(120), the script will “sleep” for 120 seconds before finishing.)



macserver01:java root# javac LoadDriver.java

macserver01:java root# java LoadDriver

Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using direct result set size methods.

Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using batch-aware prepared sql extractor.

Fri Oct 14 14:08:57 EDT 2011 INFO: MySQL Enterprise Plugin for Connector/J version 1.0.0.61 started for MySQL instance 53e71576-026c-4798-961b-0025efc37db2.

Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2

HTTP/1.1 200 OK

Server: Apache-Coyote/1.1

Content-Type: application/json

Transfer-Encoding: chunked

Date: Fri, 14 Oct 2011 18:08:57 GMT
{

    "name": "53e71576-026c-4798-961b-0025efc37db2",

    "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",

    "values":     {

        "auto_explain_min_exec_time_ms": 1,

        "capture_examples": "true",

        "capture_explain": "true",

        "enabled": "true",

        "frequency": null

    }

}

Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false]

Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2

HTTP/1.1 200 OK

Server: Apache-Coyote/1.1

Content-Type: application/json

Transfer-Encoding: chunked

Date: Fri, 14 Oct 2011 18:08:57 GMT

{ "name": "53e71576-026c-4798-961b-0025efc37db2", "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2", "values": { "auto_explain_min_exec_time_ms": 1, "capture_examples": "true", "capture_explain": "true", "enabled": "true", "frequency": null } } Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false]

And here are the results as show in the Query Analyzer tab of MySQL Enterprise Monitor:

By clicking on the SELECT sleep(?) link, I can then take a look at the query information:

For more complex queries, this tool really provides a wealth of information to help you tune your queries and increase the performance of your MySQL databases. MySQL Enterprise Monitor has a wealth of great features. For more information, see http://dev.mysql.com/doc/mysql-monitor/2.3/en/index.html.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, Perl, PHP, Python, Uncategorized

Connecting to MySQL with Perl

July 27, 2011 2 Comments

When I was designing web sites, for a long time I wrote my HTML code the “hard” way – by opening a text editor and manually typing in the code (this was before I purchased Adobe DreamWeaver).

During that time of manual HTML writing, I had a project that required forms on a web page, and I needed a place to store the information. After talking with a few tech friends, I decided to use MySQL as my web site database, and Perl as my scripting language.

I had written complex Bourne shell scripts before, but Perl was something entirely new. With a little help from a buddy of mine, after a few hours I was off and running. I was amazed at how easy it was to connect to a MySQL database with Perl.

This example will show you how to use Perl to connect to a MySQL database and simply retrieve the database version using an SQL statement. This script has the same functionality as my previous post Connecting to MySQL with Python.

For this example, we will assume that you have installed Perl and MySQL. You will also need to install the DBI and the DBD::mysql modules in Perl. These modules allow you to connect to the MySQL database.

In this script, we are going to use a text file to store our database connection information, as well as a sub-routine to help retrieve our database connection information and to create the connection string for connecting to the database.

Here is the script. (The first line of the script must be the path of your Perl executable – #!/usr/bin/perl.)


- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl -w

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the <a href=http://mysql.com/>MySQL</a> database
use DBI;
use DBD::mysql;

use warnings;

#----------------------------------------------------------------------
# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessDB") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# close the accessDB file
close(ACCESS_INFO);
#----------------------------------------------------------------------

# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "SELECT VERSION()";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute();

# retrieve the values returned from executing your SQL statement
@data = $statement->fetchrow_array();
	
# print the first (and only) value from the @data array
# we add a \n for a new line (carriage return)
print "$data[0] \n";

# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------

- - - - STOP SCRIPT - - - - (do not include this line in the script)

In the sub-routine “ConnectToMySql” above, we are using a text file (named accessDB) to store our MySQL database name, host name, user name and password (we will call this our access file). In the previous post, we hard-coded the database name, host name, user name and password in the script itself. For this script, we will create a text file to store this information, and we will place the text file in the parent directory (from where the script is stored). I have my sample script in the /cgi-bin/blog/ directory, so we would place the access file in the parent directory /cgi-bin .

The purpose of this access file is to hide the connection information, but it also allows you to quickly change the information if you have a need to switch to a different database, host or user name. And you can use this same file for multiple scripts, but I would have separate scripts for each database name.

Here is a sample access file – which contains (in this order), the database name, host name, user name and password. To match the file name in the sub-routine, we are naming the file “accessDB”. This file should be saved as a text file and make sure that it has the correct read permissions (for Unix, the permission is 644).


- - - - START TEXT FILE - - - - (do not include this line in the file)
my_database_name
192.168.1.1
mysql_user
password
- - - - END TEXT FILE - - - - (do not include this line in the file)

Again, place this file in the parent directory of where your script is located. This prevents anyone from seeing the file in case you didn’t protect the directory contents from being listed via a web page.

We use the sub-routine to extract the database name, host name, user name and password from the file after reading the file into the program. You will place the sub-routine at the end of your Perl script.

Now you are ready to run the script, connect to the database, and retrieve the MySQL server version information. Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, Perl, PHP, Python, Scripting, Uncategorized

Connecting to MySQL with Python

July 20, 2011 Leave a comment

Scripting Python to connect to a MySQL database is fairly simple, even if you don’t have any much experience with Python.

You will need to have Python and MySQL installed. Once you have Python installed, you will need to import the MySQLdb and sys modules. For MySQL, you will simply need a user that has permissions to connect to the database and perform a select statement.

For this example, I am using Python v2.5, MySQL version 5.5.8., with Mac OS X 10.6.8.

Here are the links to download the files and applications necessary for this example script:
MySQL – http://dev.mysql.com/downloads
Python – www.python.org/getit/
MySQLdb module – http://sourceforge.net/projects/mysql-python/

In this post, I am not going to cover installing Python, MySQL or the Python modules, so you will need to do this on your own. There are plenty of other websites and blogs that can assist you with these installations. You will also need to know how to run a Python script on whatever OS you are using.

Once you have everything installed and tested to make sure each application is working properly, you are ready to run this script. This script will simply connect to the MySQL database and fetch the MySQL server version. You will need to substitute your own host IP address, username, password and database name in the script.

Since I am using a unix-based OS, I can simply open up a text editor (or use the vi editor) to write the script. You do not want to save the script in rich-text format, as the script will most likely fail upon execution. You want to save the script as a plain-text file. You will also want to make sure that the line “#!/usr/bin/python” is on the first line of the script.

– – – – START SCRIPT – – – – (do not include this line in the script)
#!/usr/bin/python
# version.py – Fetch and display the MySQL database server version.

# import the MySQLdb and sys modules
import MySQLdb
import sys

# open a database connection
# be sure to change the host IP address, username, password and database name to match your own
connection = MySQLdb.connect (host = “192.168.1.1”, user = “username”, passwd = “password”, db = “database_name”)

# prepare a cursor object using cursor() method
cursor = connection.cursor ()

# execute the SQL query using execute() method.
cursor.execute (“SELECT VERSION()”)

# fetch a single row using fetchone() method.
row = cursor.fetchone ()

# print the row[0]
# (Python starts the first row in an array with the number zero – instead of one)
print “Server version:”, row[0]

# close the cursor object
cursor.close ()

# close the connection
connection.close ()

# exit the program
sys.exit()

– – – – END SCRIPT – – – – (do not include this line in the script)

Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, Perl, PHP, Python, Scripting, Uncategorized

Follow Tony on Twitter
Follow @scriptingmysql
Find me on LinkedIn:
info [at] ScriptingMySQL.com

Scripting MySQL

MySQL Document Store – a quick-guide to storing JSON documents in MySQL using JavaScript and Python (and even SQL!)

Requirements

Starting MySQL Shell (mysqlsh)

table output format

JSON output format

tabbed format

vertical format

MySQL Shell – Create & Drop Schema

Searching Documents

Modifying Documents

Modifying a document – adding a key

Importing Large Data Sets

NoSQL and SQL in the same database

JSON functions

But you already use Mongo? And you have MySQL as well?

MySQL Replication – Creating a New Master/Slave Topology with or without Virtual Machines

Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication

Using the MySQL Script mysqlfailover for Automatic Failover with MySQL 5.6 GTID Replication

MySQL Workbench Utilities – Clone MySQL users with mysqluserclone

MySQL Workbench Utilities – Administer MySQL with Python Scripts

Using Java to Connect to MySQL Enterprise Monitor with Plugin for Connector/J

Connecting to MySQL with Perl

Connecting to MySQL with Python

About the author:

Interesting Links

Recent Posts

Archives

Categories

Blog Stats

ScriptingMySQL RSS Feds