Home Ленты новостей Планета MySQL
Ðåéòèíã@Mail.ru
Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • MySQL: Check who’s trying to access data they should not
    To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables: mysql> create database mydata; mysql> use mydata mysql> create table table1 (id int auto_increment primary key, name varchar(20), something varchar(20)); mysql> create table table2 (id int auto_increment primary key, name varchar(20), something varchar(20)); Now, let’s create a user : mysql> create user myuser identified by 'mypassword'; And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user: myrole1: user has access to both tables in their entirety, reads and writes myrole2: user has access only to `table2`, reads and writes myrole3: user has only access to the column `name`of `table1` and just for reads mysql> create role myrole1; mysql> grant select, insert, update on mydata.* to myrole1; mysql> create role myrole2; mysql> grant select, insert, update on mydata.table2 to myrole2; mysql> create role myrole3; mysql> grant select(name) on mydata.table1 to myrole3; Now let’s try to connect using our new user that doesn’t have any roles assigned yet: $ mysqlsh myuser@localhost Please provide the password for 'myuser@localhost': ***** MySQL   localhost:33060+   SQL  Perfect we are connected, can we see the schema and use it ? MySQL   localhost:33060+   SQL  show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.0018 sec) MySQL   localhost:33060+   SQL  use mydata MySQL Error ERROR 1044: Access denied for user 'myuser'@'%' to database 'mydata So far, so good. Let’s assigned the first role to our user: mysql> grant myrole1 to myuser; Now the user can use the role: MySQL  localhost:33060+   SQL  set role 'myrole1'; Query OK, 0 rows affected (0.0007 sec) MySQL  localhost:33060+   SQL  show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydata | +--------------------+ Now I will add some data in both tables: SQL> insert into table1 values (0, 'fred', 'aaa'), (0, 'kenny', 'bbb'); SQL> insert into table2 values (0, 'dave', 'ccc'), (0, 'miguel', 'ddd'); Of course the user can select and see everything: MySQL  localhost:33060+   mydata  SQL  select * from table1; +----+-------+-----------+ | id | name | something | +----+-------+-----------+ | 1 | fred | aaa | | 2 | kenny | bbb | +----+-------+-----------+ 2 rows in set (0.0011 sec) MySQL  localhost:33060+   mydata  SQL  select * from table2; +----+--------+-----------+ | id | name | something | +----+--------+-----------+ | 1 | dave | ccc | | 2 | miguel | ddd | +----+--------+-----------+ 2 rows in set (0.0010 sec) If you remember it, before we assigned any role to our user, we tried to use the schema and it failed. Let’s see if as the DBA having access to performance_schema we can see it: mysql> select * from performance_schema.events_errors_summary_by_user_by_error where sum_error_raised>=1 and user='myuser' and ERROR_NAME like '%DENIED%' order by LAST_SEEN desc\G ******************* 1. row ******************* USER: myuser ERROR_NUMBER: 1044 ERROR_NAME: ER_DBACCESS_DENIED_ERROR SQL_STATE: 42000 SUM_ERROR_RAISED: 1 SUM_ERROR_HANDLED: 0 FIRST_SEEN: 2019-11-15 03:25:17 LAST_SEEN: 2019-11-15 03:25:17 Now let’ s change the role for the user, for example the most restrictive one and do some operations, but before that, we need to grant all remaining roles to our user: mysql> grant myrole2, myrole3 to myuser; MySQL  localhost:33060+   mydata  SQL  set role 'myrole3'; Query OK, 0 rows affected (0.0008 sec) MySQL  localhost:33060+   mydata  SQL  select current_role(); +----------------+ | current_role() | +----------------+ | myrole3@% | +----------------+ 1 row in set (0.0008 sec) And now let’s do some queries… MySQL  localhost:33060+   mydata  SQL  select * from table1; ERROR: 1143: SELECT command denied to user 'myuser'@'localhost' for column 'id' in table 'table1' MySQL  localhost:33060+   mydata  SQL  select * from table2; ERROR: 1142: SELECT command denied to user 'myuser'@'localhost' for table 'table2' MySQL  localhost:33060+   mydata  SQL  select name from table1; +-------+ | name | +-------+ | fred | | kenny | +-------+ 2 rows in set (0.0010 sec) And running the same query as above, we can now very all those access attempts that were not granted: mysql> select * from performance_schema.events_errors_summary_by_user_by_error where sum_error_raised>=1 and user='myuser' and ERROR_NAME like '%DENIED%' order by LAST_SEEN desc\G ******************* 1. row ******************* USER: myuser ERROR_NUMBER: 1142 ERROR_NAME: ER_TABLEACCESS_DENIED_ERROR SQL_STATE: 42000 SUM_ERROR_RAISED: 1 SUM_ERROR_HANDLED: 0 FIRST_SEEN: 2019-11-15 03:40:50 LAST_SEEN: 2019-11-15 03:41:43 ******************* 2. row ******************* USER: myuser ERROR_NUMBER: 1143 ERROR_NAME: ER_COLUMNACCESS_DENIED_ERROR SQL_STATE: 42000 SUM_ERROR_RAISED: 1 SUM_ERROR_HANDLED: 0 FIRST_SEEN: 2019-11-15 03:41:38 LAST_SEEN: 2019-11-15 03:41:38 ******************* 3. row ******************* USER: myuser ERROR_NUMBER: 1044 ERROR_NAME: ER_DBACCESS_DENIED_ERROR SQL_STATE: 42000 SUM_ERROR_RAISED: 1 SUM_ERROR_HANDLED: 0 FIRST_SEEN: 2019-11-15 03:25:17 LAST_SEEN: 2019-11-15 03:25:17 In summary, it’s very easy to very who’s trying to access unauthorized data and when it happened. And don’t forget, it’s time to upgrade to MySQL 8 ! #MySQL8isGreat Related links to SQL ROLES: https://lefred.be/content/how-to-grant-privileges-to-users-in-mysql-8-0/ https://lefred.be/content/some-queries-related-to-mysql-roles/ https://lefred.be/content/mysql-8-0-roles-and-graphml/ Credits for the icons: Security Camera by Anton Barbarov from the Noun Project computer access control by monkik from the Noun Project

  • Installing MySQL on Oracle Cloud’s “Always Free” Compute Instance
    At Oracle’s OpenWorld 2019, Oracle announced an Oracle Cloud Free Tier, which includes Oracle Cloud services that will always be free – as long as you use them or keep them active. Most other hyperscale cloud vendors offer a free 12-month trial before they start charging you, so to see Oracle provide a “truly free” service is really great. And the free services are not just limited to one or two choices – you get a wide variety of free services to choose from. As of this blog post, here is a brief summary of free services (these are subject to change in the future): Databases – Your choice of Autonomous Transaction Processing or Autonomous Data Warehouse. 2 databases total, each with 1 OCPU and 20 GB storage.Storage – 2 Block Volumes, 100 GB total. 10 GB Object Storage. 10 GB Archive Storage.Compute – 2 virtual machines with 1/8 OCPU and 1 GB memory each.Additional Services – Load Balancer, 1 instance, 10 Mbps bandwidth. Monitoring, 500 million ingestion datapoints, 1 billion retrieval datapoints. Notifications, 1 million delivery options per month, 1,000 emails sent per month. Outbound Data Transfer, 10 TB per month. For this post, I am going to show you how to create an Oracle Cloud Compute Instance (virtual machine) and install the MySQL Community Edition and MySQL Enterprise Edition (license required) of MySQL on it. Installation is a fairly simple process but there are a few things to note. If you follow these steps, you should be able to have a free cloud instance up and running with MySQL installed in less than a half-hour. Creating your free Oracle Cloud instance First, you need to go to Oracle’s Cloud Free Tier website, and register. Yes, this requires that you give them your email address and a method of payment, but you won’t be charged as long as you choose the Oracle Cloud free-tier products. I am not going to go through all of the steps to register your account, but it is fairly easy. Here are a few things to note: When selecting your home region, be sure that the services you want to use are located in this region. This link will show you the available regions for the “Always Free Cloud Services”. (A region is a localized geographic area.) After you have registered, you will be taken to the login page and then you will need to enter your email address and password. After a successful login, you will be directed to the dashboard page. At the top of the page, you should see a note that states something like this – “You are in a free trial. When your trial is over, your account will be limited to Always Free resources. Upgrade at any time.” If you are part of a free trial period, this period is for a set amount of money and for a limited time. In other words, if your free trial amount is $300 and one month of services, your free trial for normal (not free) services will expire after you have consumed $300 worth of services or after 30-days. So, you aren’t limited in your initial trial to only using the “Always Free Eligible” services. And, the “Always Free” services will continue to work after the trial period – these services are “always free”. Note: Some of the images and words on the Oracle Cloud web page might change over time. Note: As you are creating your instances, be sure that you only select options that have the “Always Free Eligible” banner. Creating your compute instance You are now ready to create your first Compute instance (Virtual Machine). Click on the “Create a VM instance” box. Notice the “Always Free Eligible” banner at the top right of the box. On the “Create Compute Instance” page, the first box allows you to name your instance. If you don’t want to keep the auto-generated instance name, you should change it here. (I am going to keep the default instance name.) The default operating system or image source is Oracle Linux. If you don’t want to use Oracle Linux, click on the “Change Image Source” button, and you will see a selection of available operating systems to use. Note that not all images are part of the free version. Next, click on the “Show Shape, Network and Storage Options” to expand this selection. The first option is to choose your Availability Domain, but the free option should already be selected. Do not change this option. An Availability Domain is one or more data centers located within a region. Your Instance Type should be chosen for you, as a Virtual Machine is the only free option. Your Instance Shape should also be chosen for you. The free Instance Shape version is VM.Standard.E2.1.Micro (Virtual Machine) with 1 Core OCPU and 1 GB Memory. Under Configure Networking, you can change the virtual cloud network name if you want. I left mine as the default auto-generated name. Important – You will want to select “Assign a public IP address” to be able to connect to this instance from outside of the Oracle Cloud. If you choose “Do not assign a public IP address”, you won’t have a way to connect to this instance unless you connect through another instance from within Oracle Cloud. You are allocated 100 gigabytes of free storage under the free tier. So, under Boot Volume, you can accept the default size of 46.6 GB, or click “Custom boot volume size (in GB) and enter a smaller or larger number. I am going with the default size. Note: You are limited to two free virtual machines per account under the “Always Free” option. So, if you want to create a separate block storage later, and you use the default sizes of 46.6 GB, you will only be limited to an additional 6.8 GB of block storage. If you are going to connect to instances using Oracle Linux, CentOS or Ubuntu, you will need to create an SSH key pair. This is very simple to do. Here is one example of how to create the SSH key pair. You might have to search for how to do it for your particular operating system. The file “id_rsa.pub” is what you will need to upload. You will need to place this file in the $HOME/.ssh directory of the computer you will use to access this compute instance. You can also share this file with others who will want to access this instance. # ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/var/root/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /var/root/.ssh/id_rsa. Your public key has been saved in /var/root/.ssh/id_rsa.pub. The key fingerprint is: SHA256:od/ZPR2IcW+ml5mcWFa3ojK9c0eeTqsW9eH8T9AQzEI root@Hackintosh.local The key's randomart image is: +---[RSA 2048]----+ | .Eo. | | . o. | | . . o. o| | . . + o=+| | . S . o=O+| | . ..o.oX*B| | .ooo.o*O=| | o..oo*o| | .+.+oo| +----[SHA256]-----+ After you have created the key, you will want to drag-and-drop the SSH key file (it should be named id_rsa.pub) onto the “Drop files here” box. Or you can click “Choose Files” and navigate the window to the location of your SSH key file. For this demo, I am not going to address any of the advanced options, so I will skip this part: Finally, you need to click on the “Create” button to create your instance. You should be directed to the Work Requests page under the Instance Details page, where you will see all of the information about your instance as it is being created. Towards the bottom, you will see the progress of the instance creation: It only took a couple of minutes for my instance to be created. The instance page should refresh automatically, but you can manually refresh it. Once the instance has been created, you should see something like this. Under the Primary VNIC Information, you will see your Public IP Address. You will need this to be able to connect to the instance via SSH. Now that my instance has been created and is running, I can connect to the Oracle Cloud Compute Instance via a terminal window. I have already placed my SSH key file in the .ssh directory under my $HOME directory. $ pwd /Users/tonydarnell $ ls -l .ssh/*pub -rw-------@ 1 tonydarnell staff 403 Sep 12 10:58 .ssh/id_rsa.pub I can connect using SSH and with the user named “opc“. You will probably get a warning about the authenticity of the host, but you can just answer “yes”. $ ssh opc@150.136.167.48 The authenticity of host '150.136.167.48 (150.136.167.48)' can't be established. ECDSA key fingerprint is SHA256:9ZE+AFYo7luYoBFZhJZ0YS/W6QdQPPJOP9xItnY17+c. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '150.136.167.48' (ECDSA) to the list of known hosts. I am now connected to my Oracle Cloud Compute Instance. I can check to see which OS version I am running: $ cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.7 (Maipo) Installing the MySQL RPM packages via yum I can now install MySQL but I have to be careful – as MariaDB is the default version of MySQL installed on Oracle Linux (even though MariaDB isn’t the same as MySQL). If you try to run the regular yum install command (sudo yum install mysql), you will see that it tries to install MariaDB: Note: Be sure to type “N” at the end of this process when asked “Is this ok” so you don’t install MariaDB. $ sudo yum install mysql Loaded plugins: langpacks, ulninfo ol7_UEKR5 | 2.8 kB 00:00:00 ol7_addons | 2.8 kB 00:00:00 ol7_developer | 2.8 kB 00:00:00 ol7_developer_EPEL | 3.4 kB 00:00:00 ol7_ksplice | 2.8 kB 00:00:00 ol7_latest | 3.4 kB 00:00:00 ol7_oci_included | 2.9 kB 00:00:00 ol7_optional_latest | 2.8 kB 00:00:00 ol7_software_collections | 2.8 kB 00:00:00 (1/12): ol7_ksplice/primary_db | 257 kB 00:00:00 (2/12): ol7_addons/x86_64/updateinfo | 74 kB 00:00:00 (3/12): ol7_addons/x86_64/primary_db | 137 kB 00:00:00 (4/12): ol7_latest/x86_64/updateinfo | 2.6 MB 00:00:00 (5/12): ol7_oci_included/x86_64/primary_db | 71 kB 00:00:00 (6/12): ol7_optional_latest/x86_64/updateinfo | 868 kB 00:00:00 (7/12): ol7_software_collections/x86_64/updateinfo | 8.7 kB 00:00:00 (8/12): ol7_developer/x86_64/primary_db | 441 kB 00:00:00 (9/12): ol7_software_collections/x86_64/primary_db | 4.7 MB 00:00:00 (10/12): ol7_latest/x86_64/primary_db | 15 MB 00:00:00 (11/12): ol7_developer_EPEL/x86_64/primary_db | 11 MB 00:00:01 (12/12): ol7_optional_latest/x86_64/primary_db | 4.0 MB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package mariadb.x86_64 1:5.5.64-1.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ====================================================================== Package Arch Version Repository Size ====================================================================== Installing: mariadb x86_64 1:5.5.64-1.el7 ol7_latest 8.7 M Transaction Summary ====================================================================== Install 1 Package Total download size: 8.7 M Installed size: 49 M Is this ok [y/d/N]: N Exiting on user command Your transaction was saved, rerun it with: yum load-transaction /tmp/yum_save_tx.2019-11-04.21-11.NHFXMJ.yumtx To install a real version of MySQL, you will need to download the release package for your platform. (Full instructions may be found at https://dev.mysql.com/doc/mysql-repo-excerpt/8.0/en/linux-installation-yum-repo.html) Go to the Download MySQL Yum Repository page (https://dev.mysql.com/downloads/repo/yum/) in the MySQL Developer Zone. Select and download the release package for your platform. You should see something like this on the Yum download page. Since I am installing on Oracle Linux 7, I will want to download the RPM package for “Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent) RPM Package“. Downloading this package requires an Oracle account, so you can register for one if you don’t already have one. I will now need to copy the package over to the Oracle Cloud instance I created. I can easily do this via SFTP. You will want to set up your FTP client with the user name of opc and you will also need to use the id_rsa.pub file to connect. I am on a Mac, and I use CyberDuck, so this is what my SFTP settings look like: I created a directory in my $HOME directory and named it mysql-install-files. I placed the RPM package in this directory. You can place the files wherever you want. [opc@instance-20191113-1544 ~]$ pwd /home/opc [opc@instance-20191113-1544 ~]$ ls -l total 0 drwxrwxr-x. 2 opc opc 56 Nov 14 15:33 mysql-install-files [opc@instance-20191113-1544 ~]$ cd mysql-install-files/ [opc@instance-20191113-1544 mysql-install-files]$ ls -l total 28 -rw-rw-r--. 1 opc opc 26024 Nov 14 15:32 mysql80-community-release-el7-3.noarch.rpm I can now install the downloaded release package. If you chose a different operating system, you will want to replace the platform-and-version-specific-package-name with the name of the downloaded RPM package. And, you will want to refer to the instructions for installing MySQL for your specific OS version. [opc@instance-20191113-1544 mysql-install-files]$ sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm Loaded plugins: langpacks, ulninfo Examining mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch Marking mysql80-community-release-el7-3.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mysql80-community-release.noarch 0:el7-3 will be installed --> Finished Dependency Resolution ol7_UEKR5/x86_64 | 2.8 kB 00:00:00 ol7_UEKR5/x86_64/updateinfo | 21 kB 00:00:00 ol7_UEKR5/x86_64/primary_db | 4.0 MB 00:00:00 ol7_addons/x86_64 | 2.8 kB 00:00:00 ol7_addons/x86_64/updateinfo | 74 kB 00:00:00 ol7_addons/x86_64/primary_db | 137 kB 00:00:00 ol7_developer/x86_64 | 2.8 kB 00:00:00 ol7_developer/x86_64/updateinfo | 71 B 00:00:00 ol7_developer/x86_64/primary_db | 444 kB 00:00:00 ol7_developer_EPEL/x86_64 | 3.4 kB 00:00:00 ol7_developer_EPEL/x86_64/group_gz | 87 kB 00:00:00 ol7_developer_EPEL/x86_64/updateinfo | 4.9 kB 00:00:00 ol7_developer_EPEL/x86_64/primary_db | 11 MB 00:00:00 ol7_ksplice | 2.8 kB 00:00:00 ol7_ksplice/updateinfo | 4.8 kB 00:00:00 ol7_ksplice/primary_db | 257 kB 00:00:00 ol7_latest/x86_64 | 3.4 kB 00:00:00 ol7_latest/x86_64/group_gz | 148 kB 00:00:00 ol7_latest/x86_64/updateinfo | 2.6 MB 00:00:00 ol7_latest/x86_64/primary_db | 15 MB 00:00:00 ol7_oci_included/x86_64 | 2.9 kB 00:00:00 ol7_oci_included/x86_64/primary_db | 71 kB 00:00:00 ol7_optional_latest/x86_64 | 2.8 kB 00:00:00 ol7_optional_latest/x86_64/updateinfo | 868 kB 00:00:00 ol7_optional_latest/x86_64/primary_db | 4.0 MB 00:00:00 ol7_software_collections/x86_64 | 2.8 kB 00:00:00 ol7_software_collections/x86_64/updateinfo | 8.7 kB 00:00:00 ol7_software_collections/x86_64/primary_db | 4.7 MB 00:00:00 Dependencies Resolved ============================================================================================= Package Arch Version Repository Size ============================================================================================= Installing: mysql80-community-release noarch el7-3 /mysql80-community-release-el7-3.noarch 31 k Transaction Summary ============================================================================================= Install 1 Package Total size: 31 k Installed size: 31 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mysql80-community-release-el7-3.noarch 1/1 Verifying : mysql80-community-release-el7-3.noarch 1/1 Installed: mysql80-community-release.noarch 0:el7-3 Complete! Installing the Community version Now that I have the MySQL repository in place, I can install the MySQL Community edition server. $ sudo yum install mysql-community-server Loaded plugins: langpacks, ulninfo mysql-connectors-community | 2.5 kB 00:00:00 mysql-tools-community | 2.5 kB 00:00:00 mysql80-community | 2.5 kB 00:00:00 (1/3): mysql-connectors-community/x86_64/primary_db | 49 kB 00:00:00 (2/3): mysql-tools-community/x86_64/primary_db | 66 kB 00:00:00 (3/3): mysql80-community/x86_64/primary_db | 87 kB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package mysql-community-server.x86_64 0:8.0.18-1.el7 will be installed --> Processing Dependency: mysql-community-common(x86-64) = 8.0.18-1.el7 for package: mysql-community-server-8.0.18-1.el7.x86_64 --> Processing Dependency: mysql-community-client(x86-64) >= 8.0.11 for package: mysql-community-server-8.0.18-1.el7.x86_64 --> Running transaction check ---> Package mysql-community-client.x86_64 0:8.0.18-1.el7 will be installed --> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.18-1.el7.x86_64 ---> Package mysql-community-common.x86_64 0:8.0.18-1.el7 will be installed --> Running transaction check ---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be obsoleted --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64 --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64 ---> Package mysql-community-libs.x86_64 0:8.0.18-1.el7 will be obsoleting --> Running transaction check ---> Package mysql-community-libs-compat.x86_64 0:8.0.18-1.el7 will be obsoleting --> Finished Dependency Resolution Dependencies Resolved ====================================================================================== Package Arch Version Repository Size ====================================================================================== Installing: mysql-community-libs x86_64 8.0.18-1.el7 mysql80-community 3.7 M replacing mariadb-libs.x86_64 1:5.5.64-1.el7 mysql-community-libs-compat x86_64 8.0.18-1.el7 mysql80-community 1.3 M replacing mariadb-libs.x86_64 1:5.5.64-1.el7 mysql-community-server x86_64 8.0.18-1.el7 mysql80-community 429 M Installing for dependencies: mysql-community-client x86_64 8.0.18-1.el7 mysql80-community 38 M mysql-community-common x86_64 8.0.18-1.el7 mysql80-community 597 k Transaction Summary ====================================================================================== Install 3 Packages (+2 Dependent packages) Total download size: 473 M Is this ok [y/d/N]: Y Downloading packages: warning: /var/cache/yum/x86_64/7Server/mysql80-community/packages/mysql-community-common-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Public key for mysql-community-common-8.0.18-1.el7.x86_64.rpm is not installed (1/5): mysql-community-common-8.0.18-1.el7.x86_64.rpm | 597 kB 00:00:00 (2/5): mysql-community-libs-8.0.18-1.el7.x86_64.rpm | 3.7 MB 00:00:00 (3/5): mysql-community-libs-compat-8.0.18-1.el7.x86_64.rpm | 1.3 MB 00:00:00 (4/5): mysql-community-client-8.0.18-1.el7.x86_64.rpm | 38 MB 00:00:17 (5/5): mysql-community-server-8.0.18-1.el7.x86_64.rpm | 429 MB 00:01:31 ---------------------------------------------------------------------------------- Total 5.1 MB/s | 473 MB 00:01:32 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Importing GPG key 0x5072E1F5: Userid : "MySQL Release Engineering " Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5 Package : mysql80-community-release-el7-3.noarch (@/mysql80-community-release-el7-3.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Is this ok [y/N]: y Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mysql-community-common-8.0.18-1.el7.x86_64 1/6 Installing : mysql-community-libs-8.0.18-1.el7.x86_64 2/6 Installing : mysql-community-client-8.0.18-1.el7.x86_64 3/6 Installing : mysql-community-server-8.0.18-1.el7.x86_64 4/6 Installing : mysql-community-libs-compat-8.0.18-1.el7.x86_64 5/6 Erasing : 1:mariadb-libs-5.5.64-1.el7.x86_64 6/6 Verifying : mysql-community-client-8.0.18-1.el7.x86_64 1/6 Verifying : mysql-community-common-8.0.18-1.el7.x86_64 2/6 Verifying : mysql-community-server-8.0.18-1.el7.x86_64 3/6 Verifying : mysql-community-libs-8.0.18-1.el7.x86_64 4/6 Verifying : mysql-community-libs-compat-8.0.18-1.el7.x86_64 5/6 Verifying : 1:mariadb-libs-5.5.64-1.el7.x86_64 6/6 Installed: mysql-community-libs.x86_64 0:8.0.18-1.el7 mysql-community-libs-compat.x86_64 0:8.0.18-1.el7 mysql-community-server.x86_64 0:8.0.18-1.el7 Dependency Installed: mysql-community-client.x86_64 0:8.0.18-1.el7 mysql-community-common.x86_64 0:8.0.18-1.el7 Replaced: mariadb-libs.x86_64 1:5.5.64-1.el7 Complete! Now that we have MySQL installed, we can start the server. $ sudo service mysqld start Redirecting to /bin/systemctl start mysqld.service And I can check to make sure MySQL is running: sudo service mysqld status Redirecting to /bin/systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2019-11-14 16:26:34 GMT; 41s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 7344 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 7426 (mysqld) Status: "Server is operational" CGroup: /system.slice/mysqld.service └─7426 /usr/sbin/mysqld Nov 14 16:26:13 instance-20191113-1544 systemd[1]: Starting MySQL Server... Nov 14 16:26:34 instance-20191113-1544 systemd[1]: Started MySQL Server. During the installation process, the user ‘root’@’localhost’ is automatically created, along with a password which can be found in the MySQL error log file. To find the password, issue this command: Note: The location of the mysqld.log file may be different for your operating system. sudo grep 'temporary password' /var/log/mysqld.log 2019-11-14T16:26:25.260720Z 5 [Note] [MY-010454] [Server] \ A temporary password is generated for root@localhost: i&wCaLKQf6Tm I can now login to the MySQL server using this password. I will also need to change the password before I can do anything else within MySQL. I can do this with the ALTER USER command. $ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.18 Copyright (c) 2000, 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 '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3wP4ssW0rd678!'; Query OK, 0 rows affected (0.01 sec) If you don’t specify a password that matches the default password policy, you will get an error message like this: mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'HeyThere!'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements MySQL is now installed and ready to use. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) Enterprise Edition MySQL’s Enterprise Edition (which requires a license) may be installed in the same way, except you must download the the Enterprise Edition files via Oracle’s eDelivery web site. You will want to download at least the following files: (relative to your operating system) -rw-rw-r--. 1 opc opc 40154652 Nov 4 21:53 mysql-commercial-client-8.0.18-1.1.el7.x86_64.rpm -rw-rw-r--. 1 opc opc 623112 Nov 4 21:50 mysql-commercial-common-8.0.18-1.1.el7.x86_64.rpm -rw-rw-r--. 1 opc opc 3890924 Nov 4 21:49 mysql-commercial-libs-8.0.18-1.1.el7.x86_64.rpm -rw-rw-r--. 1 opc opc 1377676 Nov 4 21:48 mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64.rpm -rw-rw-r--. 1 opc opc 477137056 Nov 4 21:34 mysql-commercial-server-8.0.18-1.1.el7.x86_64.rpm To install, use this command: $ sudo yum install mysql-community-{server,client,common,libs}-* During the installation process, you should see something like this: $ sudo yum install mysql-commercial*{server,client,common,libs}-* Loaded plugins: langpacks, ulninfo Examining mysql-commercial-server-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-server-8.0.18-1.1.el7.x86_64 Marking mysql-commercial-server-8.0.18-1.1.el7.x86_64.rpm to be installed Examining mysql-commercial-client-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-client-8.0.18-1.1.el7.x86_64 Marking mysql-commercial-client-8.0.18-1.1.el7.x86_64.rpm to be installed Examining mysql-commercial-common-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-common-8.0.18-1.1.el7.x86_64 Marking mysql-commercial-common-8.0.18-1.1.el7.x86_64.rpm to be installed Examining mysql-commercial-libs-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-libs-8.0.18-1.1.el7.x86_64 Marking mysql-commercial-libs-8.0.18-1.1.el7.x86_64.rpm to be installed Examining mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64 Marking mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mysql-commercial-client.x86_64 0:8.0.18-1.1.el7 will be installed ---> Package mysql-commercial-common.x86_64 0:8.0.18-1.1.el7 will be installed ---> Package mysql-commercial-libs.x86_64 0:8.0.18-1.1.el7 will be installed ---> Package mysql-commercial-libs-compat.x86_64 0:8.0.18-1.1.el7 will be installed ---> Package mysql-commercial-server.x86_64 0:8.0.18-1.1.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ========================================================================================================================== Package Arch Version Repository Size ========================================================================================================================== Installing: mysql-commercial-client x86_64 8.0.18-1.1.el7 /mysql-commercial-client-8.0.18-1.1.el7.x86_64 177 M mysql-commercial-common x86_64 8.0.18-1.1.el7 /mysql-commercial-common-8.0.18-1.1.el7.x86_64 8.5 M mysql-commercial-libs x86_64 8.0.18-1.1.el7 /mysql-commercial-libs-8.0.18-1.1.el7.x86_64 17 M mysql-commercial-libs-compat x86_64 8.0.18-1.1.el7 /mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64 6.4 M mysql-commercial-server x86_64 8.0.18-1.1.el7 /mysql-commercial-server-8.0.18-1.1.el7.x86_64 2.0 G Transaction Summary ========================================================================================================================== Install 5 Packages Total size: 2.2 G Installed size: 2.2 G Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mysql-commercial-common-8.0.18-1.1.el7.x86_64 1/5 Installing : mysql-commercial-libs-8.0.18-1.1.el7.x86_64 2/5 Installing : mysql-commercial-client-8.0.18-1.1.el7.x86_64 3/5 Installing : mysql-commercial-server-8.0.18-1.1.el7.x86_64 4/5 Installing : mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64 5/5 Verifying : mysql-commercial-server-8.0.18-1.1.el7.x86_64 1/5 Verifying : mysql-commercial-libs-8.0.18-1.1.el7.x86_64 2/5 Verifying : mysql-commercial-common-8.0.18-1.1.el7.x86_64 3/5 Verifying : mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64 4/5 Verifying : mysql-commercial-client-8.0.18-1.1.el7.x86_64 5/5 Installed: mysql-commercial-client.x86_64 0:8.0.18-1.1.el7 mysql-commercial-common.x86_64 0:8.0.18-1.1.el7 mysql-commercial-libs.x86_64 0:8.0.18-1.1.el7 mysql-commercial-libs-compat.x86_64 0:8.0.18-1.1.el7 mysql-commercial-server.x86_64 0:8.0.18-1.1.el7 Complete! You will need to change the password for root as explained above. Now you have a free Oracle Cloud compute instance with MySQL running on it. The “Always Free” tier allows you to create two free instances.   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.

  • MySQL InnoDB Cluster 8.0 - A Complete Deployment Walk-Through: Part One
    MySQL InnoDB Cluster consists of 3 components: MySQL Group Replication (a group of database server which replicates to each other with fault tolerance). MySQL Router (query router to the healthy database nodes) MySQL Shell (helper, client, configuration tool) In the first part of this walkthrough, we are going to deploy a MySQL InnoDB Cluster. There are a number of hands-on tutorial available online but this walkthrough covers all the necessary steps/commands to install and run the cluster in one place. We will be covering monitoring, management and scaling operations as well as some gotchas when dealing with MySQL InnoDB Cluster in the second part of this blog post. The following diagram illustrates our post-deployment architecture: We are going to deploy a total of 4 nodes; A three-node MySQL Group Replication and one MySQL router node co-located within the application server. All servers are running on Ubuntu 18.04 Bionic. Installing MySQL The following steps should be performed on all database nodes db1, db2 and db3. Firstly, we have to do some host mapping. This is crucial if you want to use hostname as the host identifier in InnoDB Cluster and this is the recommended way to do. Map all hosts as the following inside /etc/hosts: $ vi /etc/hosts 192.168.10.40 router wordpress apps 192.168.10.41 db1 db1.local 192.168.10.42 db2 db2.local 192.168.10.43 db3 db3.local 127.0.0.1 localhost localhost.local Stop and disable AppArmor: $ service apparmor stop $ service apparmor teardown $ systemctl disable apparmor Download the latest APT config repository from MySQL Ubuntu repository website at https://repo.mysql.com/apt/ubuntu/pool/mysql-apt-config/m/mysql-apt-config/. At the time of this writing, the latest one is dated 15-Oct-2019 which is mysql-apt-config_0.8.14-1_all.deb: $ wget https://repo.mysql.com/apt/ubuntu/pool/mysql-apt-config/m/mysql-apt-config/mysql-apt-config_0.8.14-1_all.deb Install the package and configure it for "mysql-8.0": $ dpkg -i mysql-apt-config_0.8.14-1_all.deb Install the GPG key: $ apt-key adv --recv-keys --keyserver ha.pool.sks-keyservers.net 5072E1F5 Update the repolist: $ apt-get update Install Python and followed by MySQL server and MySQL shell: $ apt-get -y install mysql-server mysql-shell You will be presented with the following configuration wizards: Set a root password - Specify a strong password for the MySQL root user. Set the authentication method - Choose "Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)" MySQL should have been installed at this point. Verify with: $ systemctl status mysql Ensure you get an "active (running)" state. Preparing the Server for InnoDB Cluster The following steps should be performed on all database nodes db1, db2 and db3. Configure the MySQL server to support Group Replication. The easiest and recommended way to do this is to use the new MySQL Shell: $ mysqlsh Authenticate as the local root user and follow the configuration wizard accordingly as shown in the example below: MySQL JS > dba.configureLocalInstance("root@localhost:3306"); Once authenticated, you should get a number of questions like the following: Responses to those questions with the following answers: Pick 2 - Create a new admin account for InnoDB cluster with minimal required grants Account Name: clusteradmin@% Password: mys3cret&& Confirm password: mys3cret&& Do you want to perform the required configuration changes?: y Do you want to restart the instance after configuring it?: y Don't forget to repeat the above on the all database nodes. At this point, the MySQL daemon should be listening to all IP addresses and Group Replication is enabled. We can now proceed to create the cluster. Creating the Cluster Now we are ready to create a cluster. On db1, connect as cluster admin from MySQL Shell: MySQL|JS> shell.connect('clusteradmin@db1:3306'); Creating a session to 'clusteradmin@db1:3306' Please provide the password for 'clusteradmin@db1:3306': *********** Save password for 'clusteradmin@db1:3306'? [Y]es/[N]o/Ne[v]er (default No): Y Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 9 Server version: 8.0.18 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. <ClassicSession:clusteradmin@db1:3306> You should be connected as clusteradmin@db1 (you can tell by looking at the prompt string before '>'). We can now create a new cluster: MySQL|db1:3306 ssl|JS> cluster = dba.createCluster('my_innodb_cluster'); Check the cluster status: MySQL|db1:3306 ssl|JS> cluster.status() { "clusterName": "my_innodb_cluster", "defaultReplicaSet": { "name": "default", "primary": "db1:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "db1:3306": { "address": "db1:3306", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "db1:3306" } At this point, only db1 is part of the cluster. The default topology mode is Single-Primary, similar to a replica set concept where only one node is a writer at a time. The remaining nodes in the cluster will be readers.  Pay attention on the cluster status which says OK_NO_TOLERANCE, and further explanation under statusText key. In a replica set concept, one node will provide no fault tolerance. Minimum of 3 nodes is required in order to automate the primary node failover. We are going to look into this later. Now add the second node, db2 and accept the default recovery method, "Clone": MySQL|db1:3306 ssl|JS> cluster.addInstance('clusteradmin@db2:3306'); The following screenshot shows the initialization progress of db2 after we executed the above command. The syncing operation is performed automatically by MySQL: Check the cluster and db2 status: MySQL|db1:3306 ssl|JS> cluster.status() { "clusterName": "my_innodb_cluster", "defaultReplicaSet": { "name": "default", "primary": "db1:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "db1:3306": { "address": "db1:3306", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" }, "db2:3306": { "address": "db2:3306", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "db1:3306" } At this point, we have two nodes in the cluster, db1 and db2. The status is still showing OK_NO_TOLERANCE with further explanation under statusText value. As stated above, MySQL Group Replication requires at least 3 nodes in a cluster for fault tolerance. That's why we have to add the third node as shown next. Add the last node, db3 and accept the default recovery method, "Clone" similar to db2: MySQL|db1:3306 ssl|JS> cluster.addInstance('clusteradmin@db3:3306'); The following screenshot shows the initialization progress of db3 after we executed the above command. The syncing operation is performed automatically by MySQL: Check the cluster and db3 status: MySQL|db1:3306 ssl|JS> cluster.status() { "clusterName": "my_innodb_cluster", "defaultReplicaSet": { "name": "default", "primary": "db1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "db1:3306": { "address": "db1:3306", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" }, "db2:3306": { "address": "db2:3306", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" }, "db3:3306": { "address": "db3:3306", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.18" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "db1:3306" } Now the cluster looks good, where the status is OK and the cluster can tolerate up to one failure node at one time. The primary node is db1 where it shows "primary": "db1:3306" and "mode": "R/W", while other nodes are in "R/O" state. If you check the read_only and super_read_only values on RO nodes, both are showing as true. Our MySQL Group Replication deployment is now complete and in synced. Deploying the Router On the app server that we are going to run our application, make sure the host mapping is correct: $ vim /etc/hosts 192.168.10.40 router wordpress apps 192.168.10.41 db1 db1.local 192.168.10.42 db2 db2.local 192.168.10.43 db3 db3.local 127.0.0.1 localhost localhost.local Stop and disable AppArmor: $ service apparmor stop $ service apparmor teardown $ systemctl disable apparmor Then install MySQL repository package, similar to what we have done when performing database installation: $ wget https://repo.mysql.com/apt/ubuntu/pool/mysql-apt-config/m/mysql-apt-config/mysql-apt-config_0.8.14-1_all.deb $ dpkg -i mysql-apt-config_0.8.14-1_all.deb Add GPG key: $ apt-key adv --recv-keys --keyserver ha.pool.sks-keyservers.net 5072E1F5 Update the repo list: $ apt-get update Install MySQL router and client: $ apt-get -y install mysql-router mysql-client MySQL Router is now installed under /usr/bin/mysqlrouter. MySQL router provides a bootstrap flag to automatically configure the router operation with a MySQL InnoDB cluster. What we need to do is to specify the string URI to one of the database node as the InnoDB cluster admin user (clusteradmin).  To simplify the configuration, we will run the mysqlrouter process as root user: $ mysqlrouter --bootstrap clusteradmin@db1:3306 --directory myrouter --user=root Here is what we should get after specifying the password for clusteradmin user: The bootstrap command will assist us to generate the router configuration file at /root/myrouter/mysqlrouter.conf. Now we can start the mysqlrouter daemon with the following command from the current directory: $ myrouter/start.sh Verify if the anticipated ports are listening correctly: $ netstat -tulpn | grep mysql tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 14726/mysqlrouter tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 14726/mysqlrouter tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 14726/mysqlrouter tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 14726/mysqlrouter Now our application can use port 6446 for read/write and 6447 for read-only MySQL connections. Connecting to the Cluster Let's create a database user on the master node. On db1, connect to the MySQL server via MySQL shell: $ mysqlsh root@localhost:3306 Switch from Javascript mode to SQL mode: MySQL|localhost:3306 ssl|JS> \sql Switching to SQL mode... Commands end with ; Create a database: MySQL|localhost:3306 ssl|SQL> CREATE DATABASE sbtest; Create a database user: MySQL|localhost:3306 ssl|SQL> CREATE USER sbtest@'%' IDENTIFIED BY 'password'; Grant the user to the database: MySQL|localhost:3306 ssl|SQL> GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@'%'; Now our database and user is ready. Let's install sysbench to generate some test data. On the app server, do: $ apt -y install sysbench mysql-client Now we can test on the app server to connect to the MySQL server via MySQL router. For write connection, connect to port 6446 of the router host: $ mysql -usbtest -p -h192.168.10.40 -P6446 -e 'select user(), @@hostname, @@read_only, @@super_read_only' +---------------+------------+-------------+-------------------+ | user() | @@hostname | @@read_only | @@super_read_only | +---------------+------------+-------------+-------------------+ | sbtest@router | db1 | 0 | 0 | +---------------+------------+-------------+-------------------+ For read-only connection, connect to port 6447 of the router host: $ mysql -usbtest -p -h192.168.10.40 -P6447 -e 'select user(), @@hostname, @@read_only, @@super_read_only' +---------------+------------+-------------+-------------------+ | user() | @@hostname | @@read_only | @@super_read_only | +---------------+------------+-------------+-------------------+ | sbtest@router | db3 | 1 | 1 | +---------------+------------+-------------+-------------------+ Looks good. We can now generate some test data with sysbench. On the app server, generate 20 tables with 100,000 rows per table by connecting to port 6446 of the app server: $ sysbench \ /usr/share/sysbench/oltp_common.lua \ --db-driver=mysql \ --mysql-user=sbtest \ --mysql-db=sbtest \ --mysql-password=password \ --mysql-port=6446 \ --mysql-host=192.168.10.40 \ --tables=20 \ --table-size=100000 \ prepare To perform a simple read-write test on port 6446 for 300 seconds, run: $ sysbench \ /usr/share/sysbench/oltp_read_write.lua \ --report-interval=2 \ --threads=8 \ --time=300 \ --db-driver=mysql \ --mysql-host=192.168.10.40 \ --mysql-port=6446 \ --mysql-user=sbtest \ --mysql-db=sbtest \ --mysql-password=password \ --tables=20 \ --table-size=100000 \ run For read-only workloads, we can send the MySQL connection to port 6447: $ sysbench \ /usr/share/sysbench/oltp_read_only.lua \ --report-interval=2 \ --threads=1 \ --time=300 \ --db-driver=mysql \ --mysql-host=192.168.10.40 \ --mysql-port=6447 \ --mysql-user=sbtest \ --mysql-db=sbtest \ --mysql-password=password \ --tables=20 \ --table-size=100000 \ run Conclusion That's it. Our MySQL InnoDB Cluster setup is now complete with all of its components running and tested. In the second part, we are going to look into management, monitoring and scaling operations of the cluster as well as solutions to a number of common problems when dealing with MySQL InnoDB Cluster. Stay tuned!   Tags:  MySQL innodb group replication deployment

  • MySQL Clone Plugin Speed Test
    In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of  Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools. Below tools are used for speed comparison of Backup and Recovery, Clone-Plugin Xtrabackup mysqldump mydumper with myloader mysqlpump Test cases: Hardware Configuration: Two standalone servers are picked up with identical configuration. Server 1 * IP: 172.23.26.127 * CPU: 2 Cores * RAM: 4 GB * DISK: 200 GB SSD Server 2 * IP: 172.21.3.114 * CPU: 2 Cores * RAM: 4 GB * DISK: 200 GB SSD Workload Preparation: On Server 1 (172.23.26.127), We have loaded approx 122.40 GB data. Now, We want to restore the data from Server 1 (172.23.26.127) to Server 2 (172.21.3.114). MySQL Setup MySQL Version: 8.0.17 InnoDB Buffer Pool Size: 1 GB InnoDB Log File Size: 16 MB Binary Logging: On Before starting every test, MySQL server is rebooted. Sysbench is setup to generate active writes across 10 tables. # sysbench oltp_insert.lua --table-size=2000000 --num-threads=2 --rand-type=uniform --db-driver=mysql --mysql-db=sysbench --tables=10 --mysql-user=test --mysql-password=****** prepare Initializing worker threads... Creating table 'sbtest1'... Creating table 'sbtest2'... Inserting 2000000 records into 'sbtest1' Inserting 2000000 records into 'sbtest2' Creating a secondary index on 'sbtest1'... Creating a secondary index on 'sbtest2'...   Case 1: (MySQL Clone Plugin)  MySQL Clone Plugin is the first in the queue for testing. To read more on Clone Plugin Setup, Check out my Previous Blog Cloning can be initiated by firing a simple SQL as below. mysql> clone instance from mydbops_clone_user@172.23.26.127:3306 identified by 'XXXX'; Query OK, 0 rows affected (7 min 47.39 sec) Restoration: Once the clone is complete the plugin will be prepare the data and restart the mysql with in 1 minute. Logs From Performance Schema: Stage Status Start time End time DROP DATA Completed 2019-10-24 14:16:19 2019-10-24 14:16:19 FILE COPY Completed 2019-10-24 14:16:19 2019-10-24 14:23:56 PAGE COPY Completed 2019-10-24 14:23:56 2019-10-24 14:23:57 REDO COPY Completed 2019-10-24 14:23:57 2019-10-24 14:23:57 FILE SYNC Completed 2019-10-24 14:23:57 2019-10-24 14:24:06 RESTART Completed 2019-10-24 14:24:06 2019-10-24 14:24:11 RECOVERY Completed 2019-10-24 14:24:11 2019-10-24 14:24:12 Overall Duration: 7 min 47 sec Case 2: (Xtrabackup) Next one on the queue is Xtrabackup 8.0. Streaming backup has been initiated from Server 1 (172.23.26.127) to Server 2 (172.21.3.114). # xtrabackup --user=XXX --password='XXXX' --backup --no-timestamp --no-lock --stream=xbstream | ssh root@172.21.3.114 "xbstream -x -C /var/backup" start_time = 2019-10-24 07:53:02 end_time = 2019-10-24 08:10:08 It’s took around 16 min to complete. Restoration: Once the backup is complete the preparation and restoration taking around 8 min to complete. xtrabackup --prepare --target-dir /var/lib/mysql . . . FTS optimize thread exiting. Starting shutdown... Log background threads are being closed... Shutdown completed; log sequence number 31714228528 191024 08:14:10 completed OK! Overall Duration: 24 min Case 3: (mysqldump) Now it’s turn for mysqldump and backup took around 43 mins to complete. # mysqldump -u mydbops -h XXXXX -p'XXXXX' -P3306 --single-transaction --routines --events --triggers --master-data=2 --all-databases > /backup/fullbackup.sql Restoration: The backup restoration is taken around 52 min’s. Overall Duration: 95 min Case 4: (mydumper) The mydumper took 39 min to complete. mydumper is setup to run on 2 threads as it’s dual core machine. # mydumper --host=xxxx --user=mydbops --password='XXXXX' --triggers --events --routines -v 3 --outputdir=/backup/mydumper_backup Restoration : The backup restoration is taken around 46 mins to complete. # myloader --user=mydbops --password='XXXXX' --host=xxxx --directory=/backup/mydumper_backup --queries-per-transaction=5000 --threads=2 --verbose=3 -e 2> /backup/restore_sep_26.log Overall Duration: 85 min Case 5: (mysqlpump) The mysqlpump is completed with in 37 mins to complete. # mysqlpump --user=mydbops --password='XXXXX' --host=xxxx --default-parallelism=2 > pump.sql Restoration: The backup restoration is taken around 41 mins to complete # mysql --user=mydbops --password='XXXX' < /home/vagrant/pump.sql Overall Duration: 78 min Test Observations: Physical backups are faster as expected, Surprising to see clone plugin beats Xtrabackup. If you’re using MySQL 8.0, Clone plugin is worth a try, If you’re running older versions of MySQL, You can happily choose Xtrabackup. MySQL Cloning and Xtrabackup Differences: Both the Cloning and xtrabackup are physical backups (copying of data files), It can be used to perform hot backup and recovery (can be used on live databases) and the principle of backup recovery is also similar. The permission of xtrabackup backup file is equal to the permission of the person who executes the command. When restoring the instance, it needs manual chown to return to the instance permission. After cloning and backup, the permission is identical with the original data permission, and no manual chown is needed to facilitate recovery. When restoring xtrabackup, reset master needs to be executed in mysql, then set global gtid_purged=”UUID:NUMBER”. The specific value of UUID:NUMBER is the content of xtrabackup_info file in backup file ,But cloning does not need this operation step, and by default cloning can establish replication. When the backup of xtrabackup is completed, scp is usually copied to another machine to recover. It takes port 22 and MySQL’s listening port is cloned. So when the directory permissions are correct, you don’t even need the permissions to log on to the Linux server at all. Do remember Clone is supported only from MySQL 8.0.17 and xtrabackup is a full fledge backup with more robust options. Featured image by Pietro Mattia on Unsplash

  • Hash join in MySQL 8
    For a long time, the only algorithm for executing a join in MySQL has been variations of the nested loop algorithm. With the release of MySQL 8.0.18, the server can now execute joins using hash join. This blog post will have a look at how it works, when it is used, and how it compares to the old join algorithms in MySQL in terms of performance.… Facebook Twitter LinkedIn

Баннер
© 2019 0A1.RU Ключевые моменты CMS Joomla!. Все права защищены.
Joomla! — свободное программное обеспечение, распространяемое по лицензии GNU/GPL.