MySQL InnoDB Cluster: MySQL Shell starter guide

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

 Earlier this week,  MySQL Shell 1.08 has been released. This is the first Release Candidate of this major piece of MySQL InnoDB Cluster.

Some commands have been changed and some new ones were added.

For example the following useful commands were added:

  • dba.checkInstanceConfiguration()
  • cluster.checkInstanceState()
  • dba.rebootClusterFromCompleteOutage()

So let’s have a look on how to use the new MySQL Shell to create a MySQL InnoDB Cluster.

Action Plan

We have 3 blank Linux servers: mysql1, mysql2 and mysql3 all running rpm based Linux version 7 (Oracle Linux 7, CentOS 7, …).

We will install the required MySQL yum repositories and install the needed packages

We will use MySQL Shell to setup our MySQL InnoDB Cluster.

Packages

To be able to install our cluster, we will first install the repository from the MySQL release package. For more information related to MySQL’s installation or if you are using another OS, please check our online documentation.

On all 3 servers, we do:

# rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
# yum install -y mysql-community-server

The commands above, will install the MySQL Community yum repositories and install MySQL Community Server 5.7.17, being the latest GA version at the date of this post.
Now we will have to install the Shell. As this tool is not yet GA, we need to use another repository that has been installed but not enabled: mysql-tool-preview

# yum install -y mysql-shell --enablerepo=mysql-tools-preview

We are done with the installation. Now let’s initialize MySQL and start it.

Starting MySQL

Before being able to start MySQL, we need to create all necessary folders and system tables. This process is called MySQL Initialization. Let’s proceed without generating a temporary root password as it will be easier and faster for the demonstration. However, I highly recommend you to use a strong root password.

When the initialization is done, we can start MySQL. So on all the future nodes, you can proceeds like this:

# mysqld --initialize-insecure -u mysql --datadir /var/lib/mysql/
# systemctl start mysqld
# systemctl status mysqld

MySQL InnoDB Cluster Instances Configuration

We have now everything we need to start working in the MySQL Shell to configure all the members of our InnoDB Cluster.

First, we will check the configuration of one of our MySQL server. Some changes are required, we will perform them using the Shell and we will restart mysqld:

# mysqlsh
mysql-js> dba.checkInstanceConfiguration('root@localhost:3306')
...
mysql-js> dba.configureLocalInstance()
... here please create a dedicated user and password to admin the cluster (option 2) ...
mysql-js> \q

# systemctl restart mysqld

Now MySQL has all the required mandatory settings to run Group Replication. We can verify the configuration again in the Shell with dba.checkInstanceConfiguration() function.

We have now to proceed the same way on all the other nodes, please use the same credentials when you create the user to manage your cluster,  I used ‘fred@%’  as example.  As you can’t configure remotely a MySQL Server, you will have to run the Shell locally on every node to be able to run dba.configureLocalInstance() and restart mysqld.

MySQL InnoDB Cluster Creation

Now that all the nodes have been restarted with the correct configuration, we can create the cluster. On one of the instances, we will connect and create the cluster using again the Shell, I did it on mysql1 and I used its ip as it’s name resolves also on the loopback interface:

# mysqlsh
mysql-js> var i1='fred@192.168.90.2:3306'
mysql-js> var i2='fred@mysql2:3306'
mysql-js> var i3='fred@mysql3:3306'
mysql-js> shell.connect(i1)
mysql-js> var cluster=dba.createCluster('mycluster')
mysql-js> cluster.status()
...

We can now validate that the dataset on the other instances is correct (no extra transactions executed). This is done by validating the GTIDs. This can be done remotely, so I will still use the MySQL Shell session I’ve open on mysql1:

mysql-js> cluster.checkInstanceState(i2)
mysql-js> cluster.checkInstanceState(i3)

When the validation is passed successfully, it’s time to add the two other nodes to our cluster:

mysql-js> cluster.addInstance(i2)
mysql-js> cluster.addInstance(i3)
mysql-js> cluster.status()

Perfect ! We used MySQL Shell to create this MySQL InnoDB Cluster.

Now let’s see this on video with all  the output of the commands:

In the next post, I will show you how to use the Shell to automate the creation of a MySQL InnoDB Cluster using Puppet.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

5 thoughts on “MySQL InnoDB Cluster: MySQL Shell starter guide

  1. Hi Fred!

    AFAIK, a default installation of RHEL / OEL / CentOS 7 will include a “mariadb-libs-5.5” package, and the MySQL 5.7 packages will conflict with it.
    In your video, I’m missing the signs of this conflict as well as your actions to handle it.
    I know the conflict can be solved in a clean way, but I’d like to see your way of doing that.

    Regards,
    Jörg

    1. Hi Jörg,

      Yum sees the conflict and deals with it:

      [root@mysql1 ~]# yum install mysql-community-server
      Loaded plugins: fastestmirror
      mysql-connectors-community | 2.5 kB 00:00:00
      mysql-tools-community | 2.5 kB 00:00:00
      mysql57-community | 2.5 kB 00:00:00
      (1/3): mysql-tools-community/x86_64/primary_db | 32 kB 00:00:00
      (2/3): mysql-connectors-community/x86_64/primary_db | 13 kB 00:00:00
      (3/3): mysql57-community/x86_64/primary_db | 96 kB 00:00:00
      Loading mirror speeds from cached hostfile
      * base: centos.mirror.nucleus.be
      * epel: nl.mirror.babylon.network
      * extras: mirrors.ircam.fr
      * updates: mirrors.ircam.fr
      Resolving Dependencies
      --> Running transaction check
      ---> Package mysql-community-server.x86_64 0:5.7.17-1.el7 will be installed
      --> Processing Dependency: mysql-community-common(x86-64) = 5.7.17-1.el7 for package: mysql-community-server-5.7.17-1.el7.x86_64
      --> Processing Dependency: mysql-community-client(x86-64) >= 5.7.9 for package: mysql-community-server-5.7.17-1.el7.x86_64
      --> Running transaction check
      ---> Package mysql-community-client.x86_64 0:5.7.17-1.el7 will be installed
      --> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for package: mysql-community-client-5.7.17-1.el7.x86_64
      ---> Package mysql-community-common.x86_64 0:5.7.17-1.el7 will be installed
      --> Running transaction check
      ---> Package mariadb-libs.x86_64 1:5.5.50-1.el7_2 will be obsoleted
      --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
      --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
      ---> Package mysql-community-libs.x86_64 0:5.7.17-1.el7 will be obsoleting
      --> Running transaction check
      ---> Package mysql-community-libs-compat.x86_64 0:5.7.17-1.el7 will be obsoleting
      --> Finished Dependency Resolution

      Dependencies Resolved

      ============================================================================================================================================================================
      Package Arch Version Repository Size
      ============================================================================================================================================================================
      Installing:
      mysql-community-libs x86_64 5.7.17-1.el7 mysql57-community 2.1 M
      replacing mariadb-libs.x86_64 1:5.5.50-1.el7_2
      mysql-community-libs-compat x86_64 5.7.17-1.el7 mysql57-community 2.0 M
      replacing mariadb-libs.x86_64 1:5.5.50-1.el7_2
      mysql-community-server x86_64 5.7.17-1.el7 mysql57-community 162 M
      Installing for dependencies:
      mysql-community-client x86_64 5.7.17-1.el7 mysql57-community 24 M
      mysql-community-common x86_64 5.7.17-1.el7 mysql57-community 271 k

      Transaction Summary
      ============================================================================================================================================================================
      Install 3 Packages (+2 Dependent packages)

      Total download size: 190 M
      Is this ok [y/d/N]:

      However, you can still use the swap command:

      [root@mysql1 ~]# yum swap mariadb-libs mysql-community-libs

      If you have am issue with the swap command of yum, you can still use yum’s shell to create a transaction like this:

      [root@mysql1 ~]# yum -q -q shell
      > remove mariadb-libs
      > install mysql-community-libs
      > transaction run

      I hope this helps,

      Cheers,
      Fred.

      1. Hi Fred,
        thanks for this command log – it helps.
        Obviously, I don’t use yum often enough: my memory still said that yum (like rpm) will only remove other packages when called to “upgrade”, not with “install”. So that has changed.
        Regards,
        Jörg

Leave a Reply

Your email address will not be published. Required fields are marked *

recent

Last Tweets Last Tweets

Locations of visitors to this page
categories