#MySQL InnoDB Cluster

[kennak]

そろそろMHAはお役御免、InnoDB Cluster + MySQL Router + MySQL Shellで。

[B! mysql] MySQL 8.1シリーズにおけるInnoDB Clusterとリードレプリカの融合（ただしMySQL Serverは8.0でOK）

[netmarkjp]

#ばばさん通信ダイジェスト : MySQL 8.1シリーズにおけるInnoDB Clusterとリードレプリカの融合（ただしMySQL Serverは8.0でOK）

賛否関わらず話題になった/なりそうなものを共有しています。

MySQL 8.1シリーズにおけるInnoDB Clusterとリードレプリカの融合（ただしMySQL Serverは8.0でOK）

http://nippondanji.blogspot.com/2023/09/mysql-81innodb-clustermysql-server80ok.html

[db-engines-opensource]

InnoDB Cluster (High Availability setup for MySQL)

InnoDB Cluster (High Availability setup for MySQL)

InnoDB cluster is a complete high availability solution for MySQL. It uses a group replication mechanism with the help of AdminAPI. We can configure the setup very easily by using the MySQL shell utility.

We can use MySQL Router on top of the cluster to route traffic to backend database nodes. It can be used to split READ and WRITE requests using different proxy ports. It can provide more…

[readyspace]

A Breakthrough in Usability – Automatic Node Provisioning

A Breakthrough in Usability – Automatic Node Provisioning

As announced in the previous blog post, MySQL InnoDB Cluster just got a very much requested feature which makes a complete, out-of-the-box, easy-to-use and versatile HA solution – Automatic Node Provisioning.

InnoDB cluster users can now rely on it for every single step of cluster deployment and management. From instance configuration to provisioning, to cluster set up and its monitoring. By…

[globalmediacampaign]

MySQL NDB Cluster Backup & Restore In An Easy Way

In this post, we will see, how easily user can take NDB Cluster backup and then restore it. NDB cluster supports online backups, which are taken while transactions are modifying the data being backed up. In NDB Cluster, each backup captures all of the table content stored in the cluster. User can take backup in the following states: When the cluster is live and fully operational When the cluster is live, but in a degraded state: Some data nodes are down Some data nodes are restarting During read and write transactions Users can restore backups in the following cluster environments: Restore to the same physical cluster Restore into a different physical cluster Restore into a different configuration cluster i.e. backup taken from a 4 nodes cluster and restore into 8 data nodes cluster Restore into a different cluster version Backups can be restored flexibly: Restore can be run locally or remotely w.r.t the data nodes Restore can be run in parallel across data nodes Can restore a partial set of the tables captured in the backup Use cases of Backup & Restore: Disaster recovery - setting up a cluster from scratch Setup NDB Cluster asynchronous replication Recovery from user/DBA accidents like dropping of a table/database/schema changes etc During NDB Cluster software upgrade Limitations: Schemas and table data for tables stored using the NDB Cluster engine are backed up Views, stored procedure, triggers and tables/schemas from other storage engine like Innodb are not backed up. Users need to use other MySQL backup tools like mysqldump/mysqlpump etc to capture these Support for only full backup. No incremental or partial backup supported. NDB Cluster Backup & Restore concept in brief: In NDB Cluster, tables are horizontally partitioned into a set of partitions, which are then distributed across the data nodes in the cluster. The data nodes are logically grouped into nodegroups. All data nodes in a nodegroup (up to four) contain the same sets of partitions, kept in sync at all times. Different nodegroups contain different sets of partitions. At any time, each partition is logically owned by just one node in one nodegroup, which is responsible for including it in a backup.When a backup starts, each data node scans the set of table partitions it owns, writing their records to its local disk. At the same time, a log of ongoing changes is also recorded. The scanning and logging are synchronised so that the backup is a snapshot at a single point in time. Data is distributed across all the data nodes, and the backup occurs in parallel across all nodes, so that all data in the cluster is captured. At the end of a backup, each data node has recorded a set of files (*.data, *.ctl, *.log), each containing a subset of cluster data.During restore, each set of files will be restored [in parallel] to bring the cluster to the snapshot state. The CTL file is used to restore the schema, the DATA file is used to restore most of the data, and the LOG file is used to ensure snapshot consistency.Let’s look at NDB Cluster backup and restore feature through an example:To demonstrate this feature, let’s create a NDB Cluster with below environment.NDB Cluster 8.0.22 version 2 Management servers 4 Data nodes servers 2 Mysqld servers 6 API nodes NoOfReplicas = 2 If you are wondering how to setup a NDB Cluster, then please look into my previous blog here.  Step 1:Before we start the cluster, let’s modify the cluster config file (config.ini) for backup. When backup starts, it create 3 files (BACKUP-backupid.nodeid.Data, BACKUP-backupid.nodeid.ctl, BACKUP-backupid.nodeid.log) under a directory named BACKUP. By default, this directory BACKUP created under each data node data directory. It is advisable to create this BACKUP directory outside the data directory. This can be done by adding a config variable ‘BackupDataDir’ to cluster configuration file i.e. config.iniIn the below example, I have assigned a path to ‘BackupDataDir‘ in config.ini:BackupDataDir=/export/home/saroj/mysql-tree/8.0.22/ndbd/node1/data4Step 2: Let’s look at the cluster from the management client (bin/ndb_mgm): Step 3: As cluster is up and running so let’s create a database, a table and do some transactions on it. Let’s insert rows into table ‘t1’ either thru sql or thru any tools. Let’s continue the rows insertion thru sql to have a significant amount of datas in the cluster. Let’s check the rows count from table ‘t1’. From the below image, we can see that table 't1' has ‘396120’ rows in it. Step 4: Now issue a backup command from the management client (bin/ndb_mgm) while some transactions on the table ‘t1’ was going on. We will delete rows from table ‘t1’ and issue a backup command in parallel. While delete ops is going on, issue a backup command from the management client: Let’s check the new row count from table ‘t1’ after all the delete ops finished. From the below image, we can see that now the table ‘t1’ has ‘306120’ rows. Let’s look at the files backup created. As we have assigned a path to backup files so let’s discuss about these files in brief. From the above image, we can see that, for each backup, one backup directory is created (BACKUP-backupid) and under each backup directory, 3 files are created. These are:BACKUP-backupid-0.node_id.Data (BACKUP-1-0.1.Data):The above file contains most of the data stored in the table fragments owned by this node. In the above example, 1 is the backupid, 0 is a hardcoded value for future use. 1 is node_id of the data node 1. BACKUP-backupid.node_id.ctl (BACKUP-1.1.ctl): The above file contains table meta data i.e. table definitions, index definitions.BACKUP-backupid.node_id.log (BACKUP-1.1.log):This file contains all the row changes that happened to the tables while the backup was in progress. These logs will execute during restore either as roll forward or roll back depends on whether the backup is snapshot start or snapshot end. Note:User can restore from anywhere i.e. doesn’t have to be from any particular data node. ndb_restore is an NDB API client program, so can run anywhere that can access the cluster. Step 5: Upon successfully completion of a backup, the output will looks like below: From the above image, Node 1 is the master node who initiate the backup, node 254 is the management node on which the START BACKUP command was issued, and Backup 1 is the 1st backup taken. #LogRecords ‘30000’ indicate that while backup was in progress some transaction was also running on the same table. #Records shows the number of records captured across the cluster. User can see the backup status also from the “cluster log” as shown below:2021-01-12 15:00:04 [MgmtSrvr] INFO -- Node 1: Backup 1 started from node 2542021-01-12 15:01:18 [MgmtSrvr] INFO -- Node 1: Backup 1 started from node 254 completed. StartGCP: 818 StopGCP: 855 #Records: 306967 #LogRecords: 30000 Data: 5950841732 bytes Log: 720032 bytesSo this concludes our NDB Cluster backup procedure. Step 6:We will now try to restore the data from the backup taken above. Let’s shutdown the cluster, cleanup all the files except the backup files and then start the cluster with initial (with no data).Let’s restore the backup to a different cluster. From the below image, we can see that data node Id’s are different from the cluster where backup was taken. Now let’s see if our database ‘test1’ is exist in the cluster or not after initial start. From the above image, we can see that, database ‘test1’ is not present. Now let’s start our restore process from the backup image #1 (BACKUP-1). The NDB restore works in this flow: It first restore the meta data from the *.ctl file so that all the tables/indexes can be recreated in the database. Then it restore the data files (*.Data) i.e. inserts all the records into the tables in the database. At the end, it executes all the transaction logs (*.log) rollback or roll forward to make the database consistent. Since restore will fail while restoring unique and foreign key constraints that are taken from the backup image so user must disable the index at the beginning and once restore is finished, again user need to rebuild the index. Step 7:Let’s start the restoration of meta data.Meta data restore, disable index and data restore can execute at one go, or can be done in serial. This restore command can be issued from any data node or can be from a non-data node as well.In this example, I am issuing meta data restore and disable index from Data Node 1 only for once. Upon successful completion, I will issue the data restore.Data Node 1: shell> bin/ndb_restore -n node_id -b backup_id -m --disable-indexes --ndb-connectstring=cluster-test01:1186,cluster-test02:1186 –backup_path=/path/to/backup directory -n: node id of the data node from where backup was taken. Do not confuse with the data node id of the new cluster.-b: backup id (we have taken backup id as ‘1’)-m: meta data restoration (recreate table/indexes)--disable-indexes: disable restoration of indexes during restore of data--ndb-connectstring (-c): Connection to the management nodes of the cluster.--backup_path: path to the backup directory where backup files exist. The results of above meta restore from data node 1 is shown below: Let’s start the data restore on the Data Node 1.  Data Node 1:shell> bin/ndb_restore -n node_id -b backup_id -r --ndb-connectstring=cluster-test01:1186,cluster-test02:1186 –backup_path=/path/to/backup directory Below, I am trying to capture the logs from the data restore run results as it started and then at the end. From the above image, we can see that restore went successful. Restore skips restoration of system table data. System tables referred to here are tables used internally by NDB Cluster. Thus these tables should not be overwritten by the data from a backup. Backup data is restored in fragments, so whenever a fragment is found, ndb_restore checks if it belongs to a system table. If it does belong to a system table, ndb_restore decides to skip restoring it and prints a 'Skipping fragment' log message.Let’s finish all the remaining data restore from the other data nodes. These data restore can be run in parallel to minimise the restore time. Here, we don’t have to pass -m, --disable-indexes again to restore command as we need to do it only once. With the first restore completion, it has already created tables, indexes etc so no need to recreate it again and will also fail. Once all the data are restored into the table(s), we will enable the indexes and constraints again using the –rebuild-indexes option. Note that rebuilding the indexes and constraints like this ensures that they are fully consistent when the restore completes.Data Node 2:shell> bin/ndb_restore -n node_id -b backup_id -r --ndb-connectstring=cluster-test01:1186,cluster-test02:1186 –backup_path=/path/to/backup directoryData Node 3:shell> bin/ndb_restore -n node_id -b backup_id -r --ndb-connectstring=cluster-test01:1186,cluster-test02:1186 –backup_path=/path/to/backup directoryData Node 4:shell> bin/ndb_restore -n node_id -b backup_id -r --ndb-connectstring=cluster-test01:1186,cluster-test02:1186 –backup_path=/path/to/backup directory Ndb restore (ndb_restore) is an API, it needs API slots to connect to cluster. Since we have initiated 3 ndb_restore programme in parallel from Data node ID 4, 5 and 6 so we can see from the below image that ndb_restore took API ID: 47, 48 and 49. Let’s see the results from the remaining data nodes. Since all the ndb_restore API finished successfully, we can see that the API ID that it had taken to connect the cluster has been released. The last step is to rebuild the index. This can also done from any data nodes or from any non-data nodes but only once.Data Node 1:shell> bin/ndb_restore -n node_id -b backup_id --rebuild-indexes --ndb-connectstring=cluster-test01:1186,cluster-test02:1186 –backup_path=/path/to/backup directory--rebuild-indexes: It enables rebuilding of ordered indexes and foreign key constraints. Step 8:So we have finished our restoration steps. Let’s check the database, table, rows count in table etc .. So database ‘test1’ is already created. Now we can see that table ‘t1’ has been created and the row count#306120 which is also matching with our backup image (look at Step# 4).So this concludes our NDB Cluster backup and restore feature. There are many more options user can pass to both backup (START BACKUP) and restore (ndb_restore) programme based on the requirements. In the above example, I have selected the basic minimum options user might need for backup and restore. For more information on these options, please refer to NDB Cluster reference manual here. https://clustertesting.blogspot.com/2021/01/mysql-ndb-cluster-backup-restore-in.html

[computingpostcom]

In this guide we shall cover the installation, configuration and usage of MariaDB 10.8 on CentOS 7 / RHEL 7. MariaDB is a ‘drop in’ replacement for MySQL. At the time of its launch, it was 99.9% compatible, and essentially a branch of MySQL. But logically, over time, the two database management systems have diverged and now there are hundreds of incompatibilities between MySQL and MariaDB databases. MariaDB is the default database solution in CentOS 7 / RHEL 7 Linux systems. This guide should only be used for new installations and not for upgrading from an older release of MariaDB server. Also note this guide is applicable for single node database server setups using MariaDB. If you’re interested in a highly available, multi-node deployment, consider using MariaDB Galera Cluster setup. Some of the good features and improvements in MariaDB 10.8 are; InnoDB redo log improvements – autosize innodb_buffer_pool_chunk_size and Improve the redo log for concurrency Lag free ALTER TABLE in replication – ALTER TABLE now gets replicated and starts executing on replicas when it starts executing on the primary, not when it finishes. Auto create partition – You can now use an AUTO keyword to inform the server to create more history partitions automatically JSON Histograms – Histograms in the statistics tables are more precise and stored as JSON, not binary Descending indexes – Individual columns in the index can now be explicitly sorted in the ascending or descending order. Spider Storage Engine Improvements – You can now declare Spider connections using the REMOTE_SERVER, REMOTE_DATABASE, and REMOTE_TABLE attributes and not abuse the COMMENT field for that. mysqlbinlog GTID support – mariadb-binlog (previously mysqlbinlog) now supports both filtering events by GTID ranges through –start-position and –stop-position, and validating a binary log’s ordering of GTIDs through –gtid-strict-mode Improved i18n support on Windows – On Windows 10+ systems, several problems with Unicode input and output in client were fixed Stored Procedures INOUT Parameters – Added support support for IN, OUT and INOUT parameter qualifiers And many Misc. features added as well Install MariaDB 10.8 on CentOS 7 / RHEL 7 The steps provided in this section should help you with the installation, configuration and usage of MariaDB 10.8 on CentOS 7 / RHEL 7 Linux. The installation will fail if you have any other MySQL based database management system on your OS. Ensure the system is clean before you proceed with this guide. Update and reboot your system. sudo yum -y update sudo reboot -f Wait for your system to be rebooted then start the installation using the next step. Step 1) Add MariaDB YUM repository We’ll use official MariaDB YUM repository for RHEL based systems which is added by running the following commands. curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup sudo bash mariadb_repo_setup --mariadb-server-version=10.8 The script automates the process of setting up repository and importing MariaDB GPG keys. [info] Checking for script prerequisites. [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo [info] Adding trusted package signing keys... /etc/pki/rpm-gpg ~ ~ [info] Successfully added trusted package signing keys [info] Cleaning package cache... Loaded plugins: fastestmirror Cleaning repos: base droplet-agent extras mariadb-main mariadb-maxscale mariadb-tools updates Cleaning up list of fastest mirrors List all available repositories on the system $ sudo yum clean all $ sudo yum repolist Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: packages.oit.ncsu.edu * extras: mirror.facebook.net * updates: mirrors.tripadvisor.com repo id repo name status

base/7/x86_64 CentOS-7 - Base 10072 droplet-agent/x86_64 DigitalOcean Droplet Agent 11 extras/7/x86_64 CentOS-7 - Extras 512 mariadb-main MariaDB Server 84 mariadb-maxscale MariaDB MaxScale 4 mariadb-tools MariaDB Tools 16 updates/7/x86_64 CentOS-7 - Updates 4050 repolist: 14749 Step 2) Install MariaDB 10.8 on CentOS 7 / RHEL 7 MariaDB 10.8 packages are available on the repo we just added. Let’s install server and client packages. sudo yum install MariaDB-server MariaDB-client MariaDB-backup Confirm the version of MariaDB server and client packages being installed and proceed if okay. Dependencies Resolved ====================================================================================================================================================================================================== Package Arch Version Repository Size ====================================================================================================================================================================================================== Installing: MariaDB-backup x86_64 10.8.3-1.el7.centos mariadb-main 7.0 M MariaDB-client x86_64 10.8.3-1.el7.centos mariadb-main 15 M MariaDB-compat x86_64 10.8.3-1.el7.centos mariadb-main 2.2 M replacing mariadb-libs.x86_64 1:5.5.68-1.el7 MariaDB-server x86_64 10.8.3-1.el7.centos mariadb-main 26 M Installing for dependencies: MariaDB-common x86_64 10.8.3-1.el7.centos mariadb-main 81 k boost-program-options x86_64 1.53.0-28.el7 base 156 k galera-4 x86_64 26.4.11-1.el7.centos mariadb-main 9.9 M libaio x86_64 0.3.109-13.el7 base 24 k

libpmem x86_64 1.5.1-2.1.el7 base 59 k lsof x86_64 4.87-6.el7 base 331 k pcre2 x86_64 10.23-2.el7 base 201 k perl x86_64 4:5.16.3-299.el7_9 updates 8.0 M perl-Carp noarch 1.26-244.el7 base 19 k perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k perl-DBI x86_64 1.627-4.el7 base 802 k perl-Data-Dumper x86_64 2.145-3.el7 base 47 k perl-Encode x86_64 2.51-7.el7 base 1.5 M perl-Exporter noarch 5.68-3.el7 base 28 k perl-File-Path noarch 2.09-2.el7 base 26 k perl-File-Temp noarch 0.23.01-3.el7 base 56 k perl-Filter x86_64 1.49-3.el7 base 76 k perl-Getopt-Long noarch 2.40-3.el7 base 56 k perl-HTTP-Tiny noarch 0.033-3.el7 base 38 k perl-IO-Compress noarch 2.061-2.el7 base 260 k perl-Net-Daemon noarch 0.48-5.el7 base 51 k perl-PathTools x86_64 3.40-5.el7 base 82 k perl-PlRPC noarch 0.2020-14.el7 base 36 k perl-Pod-Escapes noarch 1:1.

04-299.el7_9 updates 52 k perl-Pod-Perldoc noarch 3.20-4.el7 base 87 k perl-Pod-Simple noarch 1:3.28-4.el7 base 216 k perl-Pod-Usage noarch 1.63-3.el7 base 27 k perl-Scalar-List-Utils x86_64 1.27-248.el7 base 36 k perl-Socket x86_64 2.010-5.el7 base 49 k perl-Storable x86_64 2.45-3.el7 base 77 k perl-Text-ParseWords noarch 3.29-4.el7 base 14 k perl-Time-HiRes x86_64 4:1.9725-3.el7 base 45 k perl-Time-Local noarch 1.2300-2.el7 base 24 k perl-constant noarch 1.27-2.el7 base 19 k perl-libs x86_64 4:5.16.3-299.el7_9 updates 690 k perl-macros x86_64 4:5.16.3-299.el7_9 updates 44 k perl-parent noarch 1:0.225-244.el7 base 12 k perl-podlators noarch 2.5.1-3.el7 base 112 k perl-threads x86_64 1.87-4.el7 base 49 k perl-threads-shared x86_64 1.43-6.el7 base 39 k socat x86_64 1.7.3.2-2.el7 base 290 k Transaction Summary ====================================================================================================================================================================================================== Install 4 Packages (+42 Dependent packages) Total download size: 73 M Is this ok [y/d/N]: y With the rpm command package details can be queried. $ rpm -qi MariaDB-server Name : MariaDB-server Version : 10.8.3 Release : 1.el7.centos Architecture: x86_64 Install Date: Wed Jul 13 22:21:21 2022

Group : Applications/Databases Size : 129149443 License : GPLv2 Signature : DSA/SHA1, Thu May 19 12:55:05 2022, Key ID cbcb082a1bb943db Source RPM : MariaDB-server-10.8.3-1.el7.centos.src.rpm Build Date : Wed May 18 13:26:15 2022 Build Host : centos74-amd64 .... Step 3) Configure MariaDB 10.8 on CentOS 7 / RHEL 7 Start and enable mariadb database service $ sudo systemctl enable --now mariadb Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service. Check service status if it’s indeed running. $ systemctl status mariadb ● mariadb.service - MariaDB 10.8.3 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled) Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Wed 2022-07-13 22:23:07 UTC; 24s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 8189 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 8163 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS) Process: 8161 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Main PID: 8174 (mariadbd) Status: "Taking your SQL requests now..." CGroup: /system.slice/mariadb.service └─8174 /usr/sbin/mariadbd Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] InnoDB: log sequence number 45505; transaction id 14 Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] Plugin 'FEEDBACK' is disabled. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] Server socket created on IP: '0.0.0.0'. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] Server socket created on IP: '::'. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] InnoDB: Buffer pool(s) load completed at 220713 22:23:07 Jul 13 22:23:07 centos7.example.io mariadbd[8174]: 2022-07-13 22:23:07 0 [Note] /usr/sbin/mariadbd: ready for connections. Jul 13 22:23:07 centos7.example.io mariadbd[8174]: Version: '10.8.3-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server Jul 13 22:23:07 centos7.example.io systemd[1]: Started MariaDB 10.8.3 database server. ... Client version checking with mariadb command: $ mariadb -V mariadb Ver 15.1 Distrib 10.8.3-MariaDB, for Linux (x86_64) using readline 5.1 Use the mariadb-secure-installation script to secure your MariaDB database server $ sudo mariadb-secure-installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation. You already have your root account protected, so you can safely answer 'n'. Switch to unix_socket authentication [Y/n] y Enabled successfully! Reloading privilege tables.. ... Success! You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! Access to MariaDB shell should only be possible with a password $ mysql -u root ERROR 1698 (28000): Access denied for user 'root'@'localhost' $ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 14 Server version: 10.8.3-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT VERSION (); +----------------+ | VERSION () | +----------------+ | 10.8.3-MariaDB | +----------------+ 1 row in set (0.000 sec) Step 4) Create a test database/user in MariaDB We can create a test database with the command below: MariaDB [(none)]> CREATE DATABASE mydb; Query OK, 1 row affected (0.001 sec) Check if the database has been created by showing all available databases. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | mydb | +--------------------+ 5 rows in set (0.000 sec) If this setup process progressed as anticipated you should have MariaDB 10.8 installed and running on CentOS 7 / RHEL 7 server or Desktop machine. If you encounter any issues let us know in the comments section.

[all-database-solutions]

Basics of MySQL InnoDB Cluster #database #databases #databaseadministration #databasemanagementsystem #databaseadministrator #databaseadmin #mysql #mssqlserver #mysqlworkbench

[amuawia]

Post has been published on http://muawia.com/geo-redundancy-for-mysql-innodb-and-ndb-clusters/

Geo-Redundancy for MySQL InnoDB And NDB Clusters

[workfromhomeyoutuber]

Percona: Database Engineer (MySQL)

Headquarters: Durham, North Carolina URL: http://www.percona.com

VOTED TOP 10 BEST REMOTE COMPANY TO WORK FOR What's the Job?

Percona Support is a global, elite team of super hero engineers who love getting to the bottom of weird and interesting database behavior. We understand our customers’ business needs and the nuances of their technical environments. This allows us to help our customers see the big picture and identify opportunities to improve their overall database infrastructures. 

This is not a boring, entry-level role where you’ll just be triaging the boring and monotonous.

You’ll be teaming with some of the most well known database ninjas in the industry who are even better teammates, friends and mentors. You’ll have the opportunity to develop your own skills and become more powerful than you can possibly imagine…. with databases. Location: Remote, work from home position anywhere within Americas or Asia Pacific region. 5 day work week (supporting US time zones) including weekend.  Participation with the on-call team schedule. 

What does it take to be a Percona Superhero?

Exceptional knowledge of both MySQL and InnoDB

Be a wizard with databases and associated technical configurations

Knowledgeable about performance tuning, query optimization, & index tuning

Experience with backup solutions such as XtraBackup, & MyDumper

Experience with HA solutions such as MHA

Detailed understanding of monitoring requirements

Demonstrable experience of administering Linux based systems

Ability to troubleshoot methodically, identifying and applying fixes for known errors, and when necessary, capacity to think outside of the box to resolve complex issues

Enthused to expand your skillset with new technologies as the industry continues to evolve

Availability to travel for the occasional team meeting, conference and customer visit

Excellent written and spoken English and ability to work effectively with English-speaking customers

It's a bonus if you have experience with these:

Galera / XtraDB Cluster, & HAProxy

Cloud Services such as AWS and RDS or hosting providers like Rackspace

Nagios, Percona Monitoring Plugins (PMM) and other monitoring tools

Configuration management solutions such as Ansible, Puppet or Chef

Percona Toolkit suite

MySQL DBA Professional Certifications

MongoDB

PostgreSQL

 MyRocks 

Kubernetes / OpenShift / Docker 

To apply: https://jobs.lever.co/percona/7fcc072e-6aef-4eca-bbe4-e78f625ad625?lever-origin=applied&lever-source%5B%5D=weworkremotely.com

from We Work Remotely: Remote jobs in design, programming, marketing and more https://ift.tt/2Uu4NvW from Work From Home YouTuber Job Board Blog https://ift.tt/3bpY7WE

[sololinuxes]

Diferencias entre MySQL y MariaDB

Diferencias entre MySQL y MariaDB. Al mencionar MySQL y MariaDB, hablamos de los sistemas de gestión de bases de datos más usados a nivel mundial. La competencia es sana siempre que sea constructiva, pero no siempre fue así, o tal vez si, lo explicamos un poco. Ulf Michael Widenius (conocido como Monty), lanzó en 1996 junto a otros colaboradores la primera versión de MySQL. Hablamos del primer sistema de gestión de bases de datos de código abierto, puedes imaginar que supuso un bombazo con rotundo éxito a nivel mundial. Poco tardaron en llover las ofertas de compra, pero en MySQL imponen sus condiciones. En 2008 MySQL se formaliza su venta a Sun Microsystems (1.000 millones de dólares), pero se obliga a Sun a comprometerse sobre la comercialización de MySQL, siempre será open source. Además, Monty y su equipo pasarán a formar parte de Sun Microsystem como responsables del desarrollo del sistema de gestión de bases de datos. Estamos en 2008. Poco duró la feliz alianza, muy poco. Apenas unos meses después, en enero de 2009, el gigante Oracle Corporation se hace con Sun Microsystem a cambio de 5.750 millones de dólares, al día siguiente Monty y su equipo de desarrolladores se marchan para montar su propia empresa. Ríos de tinta se han escrito sobre el porqué se marcharon de la nueva adquisición de Oracle, yo creo que es evidente. Oracle Corporation pagó un sobrecoste del 42% por acción del valor real, algo realmente sospechoso e inusual. El nuevo propietario tampoco quiso comprometerse sobre MySQL como 100% open, a la vista lo tenemos ya que actualmente las versiones mejoradas de MySQL tienen un alto coste. Mi opinión personal: Oracle no solo se quitó de un plumazo una competencia real, sino que se quedó con los desarrollos del sistema de gestión de bases de datos orientados a internet (sitios web). Poco después de la adquisición, Oracle lanzó la nueva versión de su base de datos Oracle con propiedades integradas específicas para sitios web. En la siguiente agregaron más de 400 funciones nuevas, que como denunció publicamente Ulf Michael provenían de MySQL y sus proyectos futuros. Monty y su equipo de desarrolladores crean una nueva empresa, y nace MariaDB, una bifurcación de MySQL de alto rendimiento en la que confían la gran mayoría de distribuciones linux actuales. Los creadores originales de MySQL expusieron sus motivos del porqué nace MariaDB. Los principales creadores de MySQL deben permanecer juntos, es la única forma posible de mantener un sistema de gestión de bases de datos, excelente. Fomentar la continuidad del desarrollo por parte de la comunidad. Garantizar que siempre esté disponible MySQL de forma gratuita. Una vez conocemos un poco de la historia de MySQL, vemos sus principales diferencias.  

Diferencias entre MySQL y MariaDB

Quien usa MariaDB y MySQL Vemos algunos de los principales clientes de los dos sistemas de bases de datos. MariaDB Grandes corporaciones y los gigantes de linux utilizan MariaDB, podemos señalar las más conocidas: Google, Craigslist, Wikipedia, archlinux, RedHat, CentOS, Fedora, Suse, Ubuntu, AWS, 1and1, BlaBlaCart, Nokia, Samsung, y muchos más. MySQL Muchas de las grandes empresas que usan MySQL, no tienen fácil su migración a otro sistema. La lista es larga: GitHub, US Navy, NASA, Tesla, Netflix, WeChat, Facebook, Zendesk, Twitter, Zappos, YouTube, Spotify, y muchos más.   Compatibilidad entre MySQL y MariaDB MySQL: MySQL es un sistema de gestión de bases de datos relacionales (RDBMS). Al igual que otros sistema similares usa tablas, restricciones, disparadores, roles, procedimientos almacenados y vistas como componentes centrales de trabajo. Cada tabla se compone de filas, y cada fila contiene un mismo conjunto de columnas. MySQL utiliza claves primarias para identificar cada fila (llamado registro) de la tabla, y claves externas para verificar la integridad referencial de dos tablas relacionadas. MariaDB: Como MariaDB es una bifurcación de MySQL, la estructura de la base de datos y los índices son los mismos. Esto permite migrar de MySQL a MariaDB sin tener que modificar nuestras aplicaciones, los datos y estructuras serán los mismos. La conclusión es: Los archivos de configuración de datos y las tablas son compatibles. Las estructuras, protocolos y las API de cliente son las mismas. Los conectores MySQL también trabajan con MariaDB. El equipo de desarrollo de MariaDB intenta mantener una compatibilidad directa, mensualmente se compara y combina el código MariaDB con el código MySQL. Incluso la mayoría de comandos son los mismos, como mysqldump o mysqladmin.Aun así, existen diferencias entre MariaDB y MySQL que podrían llegar a causar problemas de compatibilidad mínimos. La migración de MySQL a MariaDB por parte de potencias empresariales como, Samsung o Google, no sentó nada bien en el seno de Oracle. Ahora mismo intentan diferenciarse de forma gradual, y un buen ejemplo lo encontramos en el diccionario de datos internos que se desarrolló para MySQL 8, que altera la forma en que los metadatos se guardan y utilizan. MariaDB no tiene esa función, y esto puede ser el principio del fin de la compatibilidad a nivel de archivo de datos entre MySQL y MariaDB.

Principales diferencias entre MariaDB y MySQL   Rendimiento y los índices Es evidente que el sistema de índices mejoran el rendimiento de la base de datos, ya que permite al servidor localizar y recuperar filas de manera mucho más rápida. Pero ojo, los índices pueden llegar a sobrecargar el servidor de bases de datos, se deben usar con prudencia. A pesar de lo dicho, el índice es indispensable. Si no tuviéramos índice, una búsqueda comenzaría por la primera fila de una tabla y correlativamente seguiría buscando hasta encontrar el objeto. El tiempo de espera se podría demorar en exceso. Como norma general los índices MySQL y MariaDB (PRIMARY KEY, UNIQUE, INDEX y FULLTEXT) se guardan como B-trees (Árbol-B). Existen excepciones como índices para tipos de datos espaciales, estos usan Árboles-R. También se admiten índices hash, y el motor InnoDB que utiliza listas invertidas para los índices FULLTEXT.   Diferencias en la sintaxis Las consultas son exactamente las mismas, en este apartado no tenemos nada que reprochar. Como ejemplo vemos cómo seleccionar registros de la tabla clientes. # MariaDB SELECT * FROM clientes; # MySQL SELECT * FROM clientes;   Sistemas compatibles con MariaDB y MySQL Dónde y cómo instalar MySQL El sistema MySQL está escrito en C y C ++, y tiene binarios para los sistemas: Microsoft Windows, OS X, Linux, AIX, BSDi, FreeBSD, HP-UX, IRIX, NetBSD, Novell Netware y otros. Puedes descargar MySQL desde su página oficial de descargas. Existe mucha documentación e instrucciones para los sistemas operativos más utilizados, puedes descargarlos aquí. Dónde y cómo instalar MariaDB MariaDB está escrito en C, C ++, Bash y Perl, y tiene soporte para los siguientes sistemas: Microsoft Windows, Linux, OS X, FreeBSD, OpenBSD, Solaris, y muchos más. Puedes descargar MariaDB desde su página oficial, pero al ser un sistema tan extendido en la comunidad linux, seguro que la encontraras en los repositorios de tu distribución linux. Su documentación oficial es amplia, pero mi recomendación es que acudas a la comunidad de tu distro, seguro que podrás aclarar tus dudas, ademas y no menos importante en tu idioma materno. Recuerda que MariaDB se diseño para ser el reemplazo comunitario de MySQL, el proceso de desinstalar MySQL e instalar MariaDB es bastante simple. Nunca olvides que después de ejecutar la operación debes usar mysql_upgrade.   Agrupación y replicación Qué es la agrupación La agrupación en clúster de bases de datos, es el uso de almacenamiento compartido y más servidores front-end. Los servers front-end comparten una dirección IP y el nombre de la red del clúster, que los usuarios utilizan. Se calcula cual de ellos atiende la solicitud de clientes actual. Qué es la replicación La replicación es el proceso que nos permite tener varias copias generadas automáticamente, de las bases de datos. Se conocen como "maestras" y "esclavas" y sus beneficios son importantes. Mejor soporte. Mejora considerable del rendimiento al tener la carga distribuida. Permite operar en una bases de datos esclava, sin reducir el rendimiento de la base de datos principal (cargas intensivas o de larga duración). MySQL La replicación en MySQL es asincrónica y unidireccional, un servidor actúa como maestro y otros como esclavos. Permite replicar todas las bases de datos, bases de datos específicas, e incluso una sola tabla. MySQL Cluster proporciona el soporte para los clústeres compartidos, incluye la auto-fragmentación para un correcto mantenimiento del sistema de gestión MySQL. MariaDB MariaDB también ofrece replicación maestro-maestro y maestro-esclavo. A partir de MariaDB 10.1, Galera se incluye como clúster en MariaDB. Habilitar la agrupación es muy fácil, tan solo debemos configurar unos pocos parámetros.   Conectores de bases de datos Los conectores son estándares de acceso, que tienen como objetivo hacer posible el acceso a un dato de la de de datos. Conectores de MySQL MySQL ofrece una variedad de conectores de bases de datos, se incluyen: C, C ++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D y Erlang. Conectores de MariaDB MariaDB también tiene una gran variedad de conectores, como: ADO.NET, C, C ++, D, Java, JavaScript, ODBC, Perl, PHP, Python, Ruby y Visual Studio.   De donde viene el nombre de MySQL, MariaDB y MaxDB Curiosa anécdota, jajaj. Todo es idea del creador original, está claro que hablamos de Michael Widenius (Monty) y de sus hijos. MySQL: la primera hija de Monty se llama "My", un curioso nombre tailandés. MaxDB: MaxDB es otro sistema de gestión creado por el mismo autor, el nombre se debe a su hijo "Max". MariaDB: así se llama la hija de Monty (de su segundo matrimonio), "Maria".   Canales de Telegram: Canal SoloLinux – Canal SoloWordpress Espero que este articulo te sea de utilidad, puedes ayudarnos a mantener el servidor con una donación (paypal), o también colaborar con el simple gesto de compartir nuestros artículos en tu sitio web, blog, foro o redes sociales.   Read the full article

[globalmediacampaign]

MySQL NDB Cluster Replication Topologies (Part – I)

In this blog series, we will discuss various MySQL NDB Cluster replication topologies possible with a demonstration. We will start with a simple case i.e. one source (formerly called master) and one replica (formerly called slave).Note: With MySQL version (8.0.21), we have started changing the term “master” to “source”, the term “slave” to “replica”. So in this blog we will refer these terms ‘source’ and ‘replica’ wherever applicable.Let’s create two MySQL NDB Cluster with the following environment, from which one will be termed as ‘source’ cluster while the other one will be ‘replica’ cluster. For now, let’s stick to the identical environment from both the cluster. Later in the following blog series, we will change the environments and will run the replication.MySQL NDB Cluster version (Latest GA version)1 Management node4 Data nodes1 MysqldConfiguration slots for up to 4 additional API nodesReplication schema diagram:   CLUSTER A                                                    CLUSTER BMySQL (test01) ------->------->--------->-------->  MySQL (test05)   (source)                                                             (replica)test01: MySQL server host name of source cluster, IP: 100.103.21.66test05: MySQL server host name of replica cluster, IP: 100.103.21.65Step 1: Let’s start the ‘source’ cluster:And, then start the ‘replica’ cluster:Now both the clusters are up and running.Step 2: Configure MySQL servers for source clusterI assume that user is aware of replication fundamentals or can refer to the link to understand a bit.Let’s discuss about two MySQL configuration parameter that is required to start the replication i.e. log_bin and ndb_log_bin.log_bin (binary logging):When binary logging enabled, the server logs DDL and DML changes to the binary log. Before MySQL 8.0, binary logging was disabled by default, which means that a user must be enabled this variable (log_bin) before starting the replication. From MySQL 8.0 version, binary logging is enabled by default in the MySQL Server.ndb_log_bin:By enabling this configuration variable, any changes user will do to NDB tables will be written to binary log. The setting for this variable has no effect if binary logging is not already enabled on the server using ‘log_bin’. From NDB Cluster 8.0.16 and later, ndb_log_bin defaults to 0 (false).If log_bin=1 and ndb_log_bin=0, then each MySQLD will log only things that it is locally aware of i.e other MySQLD won’t see the changes even if there are part of the cluster. For NDB tables, only DDL statements are logged in the binlog of the MySQLD where they are executed. While for non-NDB tables (innodb), both DDL and DML statements are logged in the binlog of the MySQLD where they live.If log_bin=1 and ndb_log_bin=1 then, DDL statements executed on other MySQLDs attached to the same cluster and affecting NDB tables are logged. DML statements occurring on NDB tables (from any MySQLD / API) are also logged.So in short, if user is using MySQL NDB Cluster version above 8.0.16 then only ‘ndb_log_bin’ need to be enabled as binary logging is already enabled by default in MySQL server. For older NDB Cluster version to 8.0, log_bin configuration variable must be enabled as it is disabled by default.The easier way to set these variables is from MySQL configuration file i.e ‘my.cnf’ file and then start/restart the MySQL server.As we are using latest MySQL NDB Cluster (8.0.25) so we need to set only ‘ndb_log_bin’ from source’s MySQL server.--log-bin:In our demo, I have assigned a path to the binary log file location through a configuration variable ‘–log-bin’, by default, all binary log files are created in MySQL server data directory. I have set all the required configuration variables in ‘my.cnf’ file of source MySQL server.All the three variables above either can be set before the MySQL server start (before the cluster start) or can be added later and then a MySQL server restart is needed to take the effect as these variables are not ‘dynamic’. In our demo, I have already added these variables in ‘my.cnf’ file so we don’t have to do a MySQL server restart.Below is our source’s MySQL server config file i.e. ‘my.cnf’ where we have added these two config variables i.e. ndb-log-bin, log-bin.Step 3:a) Let’s cleanup the old binary log files if exist from the source:From the source MySQL server, let’s delete all existing binary log files if any exist. This can be done through the command ‘reset master’. After the execution of ‘reset master’ command, a new empty binary log file will be created so that binary logging can be restarted. The status of the binary log file can be known through ‘show master status’ command. User should use this command (reset master) when it is absolute necessary.mysql> reset master;mysql> show master status;b) Login as ‘root’ user in the source MySQL server. Then update the root account so that other host with ‘root’ user can connect to this source MySQL server.mysql> bin/mysql -u root -P"13010";mysql> UPDATE mysql.user SET host='%' where user='root' and host='localhost';mysql> flush privileges;In the upcoming blog series, we will see how to connect from the replica server with a new user with limited privileges rather through ‘root’ user. For simplicity, I have taken ‘root’ user account for this demonstration.Now, from the replica’s MySQL Server, try to connect to the source’s MySQL server.mysql> bin/mysql -u root -P"13010";mysql> stop replica;mysql> reset replica all;mysql> CHANGE MASTER TO MASTER_HOST='100.103.21.66', MASTER_USER='root', MASTER_PORT=13010;mysql> start replica;Stop replica: It stops the replication threads if running before.Reset replica all: This is to ensure that we start the replication from a clean system. It clears the replication metadata repositories, deletes all the relay log files, and starts a new relay log file.Change master: This is to ensure that the replica server connects to the source server with the required parameters such as source IP, user name, password, port number etc. and read data from the source server.Now check the state of the replication: mysql> show replica statusGFrom the above image we can see that we have successfully created a replication between two MySQL NDB Clusters. Both Slave_IO and slave_SQL are running.Step 4:Now let’s verify the replication by creating a database, tables and few transactions from the source cluster. We will check the same from the replica’s mysql server.From the source’s MySQL server:Let’s create a database ‘test1’, table ‘t1’ and insert 14 rows to it.Let’s check from the replica’s MySQL server:Let’s see if all the database objects and rows are available at replica cluster or not. Login as root and check the row count:From the above image, we can see that a database ‘test1’, table ‘t1’ with 14 rows got created at replica cluster. Let’s check the replication status once again:From the above image, we can see the message that slave has read all the relay logs.. and waiting for the more events from the source MySQL server. That means all data from the source has been replicated successfully to replica cluster.So this verify that data created at source cluster seamlessly replicated to replica cluster. This is a very simple way to setup a replication between two NDB clusters.This concludes our discussion on first replication topology in NDB Cluster.                                                                                                     https://clustertesting.blogspot.com/2021/05/mysql-ndb-cluster-replication.html

[computingpostcom]

MySQL Replication is the process whereby a single data set stored in a given MyQSL database server, known as the source will be live-copied to another server known as the replica. Replication is asynchronous by default, although, there are other methods of synchronization supported such as; one-way asynchronous replication, semi-synchronous replication, delayed replication e.t.c. It is possible to replicate all databases and tables within a database depending on your configuration. Replication in MySQL is advantageous in the following ways: It offers data security – it is possible to run a backup on the replica without corrupting the corresponding source data Long-distance data distribution – It makes it possible to create a local copy of data on a remote server/site Analytics – live data can be stored on the source and analytic information obtained from the replica’s database. Offers scale-out solutions – the load is spread among multiple replicas, this, in turn, improves performance. There are two types of replication setups: Master-slave replication – In this setup, the data stored in the master will be live-copied to the slave Master-master replication – Here, data can be copied from either server to the other one. This setup adds redundancy and increases efficiency, especially when dealing with accessing the data Multi-source replication. Star replication. In this guide, We will walk through how to configure MariaDB Master-Master replication on Ubuntu 20.04|18.04. MariaDB is preferred here, due to the following amazing features: PHP, one of the most popular web development languages, is supported by MariaDB. MariaDB makes advantage of a well-known and widely used querying language. MariaDB supports a wide range of programming languages and operates on a variety of operating systems. It provides a variety of storage engines, including high-performance storage engines such as XtraDB and InnoDB Supports Galera cluster technology – a multi-master cluster based on synchronous replication It has a sequence storage engine that allows one to create ascending or descending sequences of numbers. Getting Started In this guide, we will have two Ubuntu 20.04|18.04 servers, both serving as masters in the replication. Hostname IP_Address Task master1.computingforgeeks.com 192.168.100.50 MariaDB Master node 1 master2.computingforgeeks.com 192.168.100.51 MariaDB Master node 2 Set the hostnames of the servers. ##On Master Node 1 sudo hostnamectl set-hostname master1.computingforgeeks.com --static ##On Master Node 2 sudo hostnamectl set-hostname master2.computingforgeeks.com --static Then proceed and add the servers to the list of known hosts(on both servers) $ sudo vim /etc/hosts 192.168.100.50 master1.computingforgeeks.com master1 192.168.100.51 master2.computingforgeeks.com master2 From Master Node 1, try ping Master Node 2 as below. $ ping master2 PING master2.computingforgeeks.com (192.168.100.51) 56(84) bytes of data. 64 bytes from master2.computingforgeeks.com (192.168.100.51): icmp_seq=1 ttl=64 time=0.568 ms 64 bytes from master2.computingforgeeks.com (192.168.100.51): icmp_seq=2 ttl=64 time=0.470 ms Now update all the servers to the latest stable packages. sudo apt update && sudo apt upgrade Step 1 – Configure Chrony NTP synchronization The time between the two MariaDB master nodes must be synchronized. Install Chrony NTP packages on the servers. sudo apt -y install chrony Once installed, start and enable Chrony sudo systemctl enable --now chronyd Set your local TimeZone and configure Chrony NTP sudo timedatectl set-timezone Africa/Nairobi --adjust-system-clock sudo timedatectl set-ntp yes Verify if the time has been synchronized. $ timedatectl Local time: Wed 2022-05-04 12:53:37 EAT Universal time: Wed 2022-05-04 09:53:37 UTC RTC time: Wed 2022-05-04 09:53:38

Time zone: Africa/Nairobi (EAT, +0300) System clock synchronized: yes NTP service: active RTC in local TZ: no Step 2 – Install MariaDB on both servers Now we will ensure that both servers have MariaDB installed. First, install the required packages. sudo apt install curl software-properties-common -y In this guide, we will install MariaDB on both Ubuntu 20.04|18.04 servers from the default repositories sudo apt update sudo apt install mariadb-server mariadb-client With MariaDB installed successfully Start and enable it. sudo systemctl enable --now mariadb.service Verify the installation by logging in to the MariaDB shell as below. Provide the default password: root $ sudo mysql -u root -p Enter password: root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 43 Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1-log Ubuntu 20.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> exit sudo ufw allow 3306/tcp sudo ufw allow 4567/tcp sudo ufw allow 4568/tcp sudo ufw allow 4444/tcp Step 3 – Configure MariaDB for Master-Master Replication Now we need to edit our master nodes to enable replication. On Master Node 1. On master node 1, we will edit the config file below. sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf At the end of the file, add the below information. [mysqld] ............ bind-address = 192.168.100.50 ...... # other settings you may need to change. server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = replica #binlog_ignore_db = exclude_database_name Replace the bind address with the IP_address of your Master node 1 and replica, with the database to replicate. Save the file and restart MariaDB. sudo systemctl restart mariadb Now login to the MariaDB instance. sudo mysql -u root -p Create a master user and the required grants. CREATE USER 'master_user1'@'%' IDENTIFIED BY 'Passw0rd'; GRANT REPLICATION SLAVE ON *.* TO 'master_user1'@'%'; FLUSH PRIVILEGES; Remember to replace master_user with your desired username and Passw0rd with your preferred password for the user. Check the status to show the current binary log and the exact location where the master should start replication from. SHOW MASTER STATUS; Sample Output: MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 782 | replica | | +------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) Remember, the position of the file is important as it will be required later in this configuration. On Master Node 2. Now on the other node, Master Node 2, we will do a similar configuration as above. Edit the config file as below. sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf In the file, add the below lines replacing them appropriately. [mysqld] ............ bind-address =192.168.100.51 ....... # other settings you may need to change. server-id = 2 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = replica #binlog_ignore_db = exclude_database_name Replace the bind address with the IP_address of Master Node 2. Save the file and restart MariaDB. sudo systemctl restart mariadb Log into the MariaDB shell. sudo mysql -u root -p Also here, create the master user with the necessary privileges. CREATE USER 'master_user1'@'%' IDENTIFIED BY 'Passw0rd';

GRANT REPLICATION SLAVE ON *.* TO 'master_user1'@'%'; FLUSH PRIVILEGES; Now in the next step, we need to add the information from Master Node 1 to our Master node 2. First, stop the slave. STOP SLAVE; Now proceed and add the Master Node 1 information. CHANGE MASTER TO MASTER_HOST = "master1", MASTER_USER = "master_user1", MASTER_PASSWORD = "Passw0rd", MASTER_PORT=3306, MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 782, MASTER_CONNECT_RETRY=10; The details provided here are details for the Master Node 1, Replace mysql-bin.000001 with the path of the file and 782 with the position of the file on Master Node 1. Now start the slave. START SLAVE; Show the Master status and also take note of the path and position of the file. SHOW MASTER STATUS; Sample Output: MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 782 | replica | | +------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) Complete the Master-Master Replication setup on Master Node 1. Now we need to complete the setup by adding the Master Node 2 information to our Master node 1. While logged in to the MariaDB shell on Master node 1. Stop the replication. STOP SLAVE; Now on Master node 1 add the information for Master Node 2. CHANGE MASTER TO MASTER_HOST = "master2", MASTER_USER = "master_user1", MASTER_PASSWORD = "Passw0rd", MASTER_PORT=3306, MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 782, MASTER_CONNECT_RETRY=10; Replace the provided details appropriately and proceed as below. START SLAVE; Verify the slave status on any node. MariaDB [(none)]> show slave status\G Sample Output: On both nodes, the service should be waiting for the other to send an event as shown above. Step 4 – Test the MariaDB Master-Master replication Now that we have successfully completed all the required configurations, we need to test if the setup is working correctly. On Master node 2: On the MariaDB shell on Master Node 2, create the replication database(replica) and a new table in it. create database replica; CREATE TABLE replica.test (`name` varchar(18)); On Master node 1: Show the database and table in the replica database SHOW TABLES IN replica; Sample Output: With the above output, it is safe to assume that our MariaDB master-master replication is working perfectly. Conclusion This is the end! We have configured MariaDB Master-Master replication on Ubuntu 20.04|18.04. You can use MariaDB replication to solve innumerable problems from performance, supporting backup of databases to alleviating system failures. Cheers!

[all-database-solutions]

Restoring a Failed member in InnoDB Cluster

Restoring a Failed member in InnoDB Cluster For MySQL DBA its a common issue that one of the MySQL InnoDB Cluster node got corrupted. In that case we need to restore MySQL cluster node from mysqlbackup. Usually backup in MySQL InnoDB cluster is taken from one of the node in the cluster, if the node corrupted and backup node are same then you can simply restore the backup and start group replication. But if the backup is node taken from the corrupted node ,We need to take some additional steps. In this blog we will discuss about the same scenario. Assume that we have three MySQL node mysqlhost01,mysqlhost02 & mysqlhost03. And the node which got corrupted is mysqlhost03 and backup is taken from the mysqlhost02.We will follow below steps to restore failed node mysqlhost03: 1) Move the MySQL backup from mysqlhost02 to mysqlhost03. 2) Change parameter group_replication_start_on_boot to false.set parameter group_replication_start_on_boot to false so that post MySQL restart Group replication services are not started automatically. Connect to mysqlhost03Server: mysql>select * from performance_schema.persisted_variables where variable_name like 'group_replication_start_on_boot%'; mysql>set persist group_replication_start_on_boot=false; 3) Stop MySQL on node mysqlhost03 and take backup of the existing auto.cnf and mysqld-auto.cnf . This is required to preserve the UUID for the mysqlhost03. 4) Remove all the data file and other database related files.Check the variables innodb_data_home_dir, innodb_log_group_home_dir, and innodb_undo_directory and make sure there is no files in these directory else backup restoration might fail. 5) Restore the backup mysqlhost02 to mysqlhost03. (Using Restore MySQL InnoDB Cluster from mysqlbackup) mysqlbackup --defaults-file=/etc/my.cnf \ --datadir=/var/lib/mysql \ --backup-image=/backups/my.mbi_2206_1429  \ --backup-dir=/tmp/restore_`date +%d%m_%H%M` copy-back-and-apply-log The command above assumes that the binary logs and relay logs on mysqlhost02 and mysqlhost03 have the same base name and are at the same location on the two servers. If these conditions are not met, you should use the --log-bin and --relay-log options to restore the binary log and relay log to their original file paths on mysqlhost03 . For example, if you know that on mysqlhost03  the binary log's base name is mysqlhost03 -bin and the relay-log's base name is mysqlhost03 -relay-bin, your restore command should look like:mysqlbackup --defaults-file=/etc/my.cnf \  --datadir=/var/lib/mysql \  --backup-image=/backups/my.mbi_***_****  \  --log-bin=mysqlhost03-bin --relay-log=mysqlhost03-relay-bin \  --backup-dir=/tmp/restore_`date +%d%m_%H%M` copy-back-and-apply-log 6) Restore auto.cnf and mysqld-auto.cnf files backed up step 3.For automatic rejoining of the restore node mysqlhost03 the UUID and other system variable must be same as is was before restore. In order to achieve same we need to restore the auto.cnf and mysqld-auto.cnf file. 7) Start MySQL on mysqlhost03 and verify parameter gtid_execute.MySQL> systemctl start mysqld Check the value of the gtid_execute and gtid in the backup_gtid_executed.sql file . These two values must be same, In order to start replication from the correct gtid value. If these value are not same,then the change the gtid_execute value to same as that of the value in the backu_gtid_executed.sql. mysql> SET SQL_LOG_BIN=OFF; mysql> SOURCE backup_gtid_executed.sql mysql> SET SQL_LOG_BIN=ON; Continue Reading... 

[siva3155]

300+ TOP MYSQL Objective Questions and Answers

MYSQL Multiple Choice Questions :-

1. The "father" of MySQL is ______. A. Michael Widenius B. Bill Joy C. Bill Gates D. Stephanie Wall E. Sigmund Velin Ans:  A 1: The main MySQL program that does all the data handling is called A. mysql B. mysqld C. mysql.exe D. httpd Ans:  B 2: To use MySQL on your computer, you'll need A. Perl, PHP or Java B. Some sort of client program to access the databases C. A Browser D. FTP and Telnet Ans:  B 3: MySQL comes with as standard with client libraries for: A. C only B. Java only C. Java and C D. Perl, PHP and C E. C, C# and the .NET environment, Java and Python Ans: A 4. What kind of replication is supported by the MySQL server? A. Multiple-master replication B. Master to slave replication C. Single file based clustering D. MySQL doesn't support replication Ans:  B 5: Internally information is held in tables. Which of these is NOT a valid format: A. BDB B. Isam99 C. MyIsam D. Hash E. Isam F. InnoDb Ans:  B 6: Commands passed to the MySQL daemon are written in A. Your choice from Perl, PHP, Java or some other languages B. English C. the Structured Query Language D. Swedish Ans: C 7: MySQL supports the complete SQL99 standard A. true B. false Ans: B 8: Which of these is not a valid name for a column A. Far B. Near C. To D. From Ans: B 9: In a LIKE clause, you can could ask for any value ending in "ton" by writing A. LIKE ton$ B. LIKE ^.*ton$ C. LIKE %ton D. LIKE *ton E. LIKE .*ton Ans:  C 10: Which of the following can add a row to a table? A. Alter B. Add C. Insert D. Create E. Update F. Commit Ans:  C

MYSQL MCQs 11: Which of these commands will delete a table called XXX if you have appropriate authority: A. DROP XXX B. DELETE XXX WHERE confirm = "YES" C. DROP XXX WHERE confirm = "YES" D. DROP TABLE XXX E. DELETE XXX F. DELETE TABLE XXX Ans:  D 12: The USE command A. Is a pseudonym for the SELECT command B. Has been deprecated and should be avoided for security reasons C. Should be used to choose the database you want to use once you've connected to MySQL D. Is used to load code from another file Ans:  C 13: In a LIKE clause, you can ask for any 6 letter value by writing: A. LIKE ?????? B. LIKE .{6} C. LIKE ^.{6}$ D. LIKE ...... (that's six dots) E. LIKE ______ (that's six underscore characters) Ans:  E 14: MySQL runs on which operating systems? A. Unix and Linux only B. Linux and Mac OS-X only C. Unix, Linux, Windows and others D. Any operating system at all Ans:  C 15: MySQL Access security is controlled through A. MySQL login accounts, and priveliges set for each account B. The ID that the user logged into the server through, and priveliges set up for that account. C. A table of valid IP addresses, and priveliges set up for each IP address D. The normal login security is sufficient for MySQL, and it does not have any extra controls of its own. Ans:  A 16: Which of the following is NOT supported by MySQL A. Temporary (Hash) Tables B. Table Joining C. Stored Procedures D. Regular Expression Matching Ans:  C 17: One of the early proponents of relational database who laid down many of the principles we use to this day was: A. Xigang Koi B. Mahatma Coate C. Edgar Codd D. William Crawford E. Robert Langton Ans:  C 18: You are required to buy a license for MySQL if A. If you use it for longer than 30 days B. You use MySQL in a live application and not just for evaluation C. You incorporate it into a product that you sell D. You use it on a publicly accessible site E. You are never required to buy a license Ans:  C 19: Which of these is a valid call to a function (watch the spaces carefully!) A. CONCAT(  A  ,  B  ) B. CONCAT  (  A  ,  B  ) C. CONCAT(  "A"  ,  "B"  ) D. CONCAT  (  "A"  ,  "B"  ) E. CONCAT  (A,  B) F. CONCAT  ("A",  "B") Ans:  C 20: If you want to undo a GRANT, you should use A. REVOKE B. UNDO C. UNGRANT D. DELETE E. WITHDRAW F. ROLLBACK Ans:  A 21: How many distinct, different values can you hold in an enum field? A. 255 B. 7 C. 65535 D. 2 (True and False) Ans:  C 22: Which of the following is NOT available in MySQL: A. REVOKE B. FETCH C. LIKE D. JOIN E. SELECT Ans:  B 23: Which of these field types would be best to hold a film title? A. longblob B. tinytext C. mediumtext D. longtext E. tinyblob Ans:  B 24: The program called mysql is A. There isn't a program just called mysql B. A wrapper through which Java clients must connect to the databases C. A client program that lets you send SQL commands to the database engine D. The database engine E. A program that starts up and shuts down the database engine Ans:  C 25: In a LIKE clause, you can ask for any 6 letter value by writing: A. LIKE ______ (that's six underscore characters) B. LIKE .{6} C. LIKE ?????? D. LIKE ^.{6}$ E. LIKE ...... (that's six dots) Ans:  A 26: Which of the following is available in MySQL: A. CREATE VIEW B. CREATE SCHEMA C. CREATE TRIGGER D. CREATE DATABASE E. CREATE FAIRYLIGHTS Ans:  D 27: Which of these field types would be best to hold a .jpg image? A. char binary B. nchar binary C. text D. blob Ans:  D 28: If you're writing a client using the C language API, your source code should include the following line at the top: A. use mysql; B. include ("mysql.h"); C. #include D. import com.mysql.*; Ans:  C 29: A user with RELOAD priveliges can A. Can reset passwords for any mysql user account B. Perform certain "admin type" operations such as flush table and logs from memory to disc, but can't normally do anything "dangerous" C. Can repopulate the databases and tables from a backup D. Can restart the MySQL daemon in addition to any other priveliges he might also have Ans:  B 30: What's the difference between the DESCRIBE and SHOW FIELDS FROM commands? A. Only SHOW FIELDS FROM can take a LIKE clause B. Nothing - they're the same thing C. Only DESCRIBE can take a LIKE clause Ans:  B 31: Which of these is not a valid name for a column A. To B. From C. Far D. Near Ans:  B 32: Commands passed to the MySQL daemon are written in A. English B. Your choice from Perl, PHP, Java or some other languages C. the Structured Query Language D. Swedish Ans:  C 33: Which of the following is available in MySQL: A. CREATE TRIGGER B. CREATE SCHEMA C. CREATE FAIRYLIGHTS D. CREATE DATABASE E. CREATE VIEW Ans:  D 34: One of the early proponents of relational database who laid down many of the principles we use to this day was: A. Edgar Codd B. Xigang Koi C. Robert Langton D. Mahatma Coate E. William Crawford Ans:  A MYSQL Questions and Answers pdf Download Read the full article

[readyspace]

MySQL InnoDB Cluster – Automatic Node Provisioning

MySQL InnoDB Cluster – Automatic Node Provisioning

The MySQL Development Team is very excited and proud of what was achieved in this 8.0.17 GA release!

The spotlight is on… A game-changer feature – Automatic Node provisioning!

This has been an extremely desired and important feature, and it has been accomplished, once again, with tight integration and cooperation of MySQL Components:

The new MySQL Clone Plugin: To take a physical snapshot of the…