'MariaDB News & Article'에 해당되는 글 6건

  1. [MariaDB] How to manage MariaDB with Puppet
  2. [MariaDB] Upgrading MySQL 5.1 to MariaDB 10.0 on CentOS 6
  3. [MariaDB] How to search for and replace a text string in MariaDB on Linux
  4. [MariaDB] MariaDB Replication, MaxScale and the need for a binlog server
  5. [MariaDB] How to migrate from MySQL to MariaDB on Linux in five steps
  6. [MariaDB] Maria DB에 대하여...





ANATOLIYDIMITROV

Puppet is a powerful automation tool that helps administrators manage complex server setups centrally. You can use Puppet to manage MariaDB — let's see how.

With Puppet, you describe system states that you want the Puppet master server [to enforce] on the managed nodes. If you don't have Puppet installed and configured yet, please check the official Puppet documentation.

Before you can use Puppet to manage MariaDB, you must install a Puppet module that sets the proper repository corresponding to your operating system and version of MariaDB. For Red Hat-based distros, including CentOS, you can use the Yguenane MariaDB [repository] Puppet module. On your Puppet master, install the module with the command puppet module install yguenane/mariadbrepo, which puts the module files in the directory /etc/puppet/modules/mariadbrepo/.

The Yguenane module currently supports only Red Hat 5 and 6, CentOS 5 and 6, and any Fedora version that has the MariaDB repository, which, per the MariaDB 10.0 repository, means versions 19 and 20. If you need support for different versions or operating systems, you must edit the module. Its code is simple and straightforward, so you should be able to adapt it even if you don't know Ruby, the programming language behind Puppet. For example, to add support for Red Hat or CentOS 7, edit the file /etc/puppet/modules/mariadbrepo/manifests/init.pp and change the $os_ver variable. Initially, it looks like this:

$os_ver = $::operatingsystemrelease ? {
    /6.[0-9]/  => '6',
    /5.[0-9]+/ => '5',
    default    => $::operatingsystemrelease,
    }

Change it to:

$os_ver = $::operatingsystemrelease ? {
    /7.[0-9]/  => '7',
    /6.[0-9]/  => '6',
    /5.[0-9]+/ => '5',
    default    => $::operatingsystemrelease,
}

You can edit other variables in the same file, such as $os, to add support for other operating systems. As long as there is an official MariaDB repository for the OS and version, you should be able to add support for it.

MariaDB installation on the Puppet nodes

Once you have the necessary Puppet module on the Puppet master, you can install MariaDB on the Puppet nodes. Let's assume your Puppet manifests are found in the default /etc/puppet/manifests/site.pp file. The first thing you should do is distribute the MariaDB repo to the Puppet nodes that should have MariaDB installed. You can do it by adding code similar to this to site.pp, or to a separate file that you include in site.pp:

node 'host1' {
    class {'mariadbrepo':
        version => '10.0',
    }
}
node 'host2' {
    class {'mariadbrepo':
        version => '10.1',
    }
}

This tells Puppet to use the repository for MariaDB version 10.0 for host1, and the one for MariaDB 10.1 for host2. This is just an example to show that you can have different versions on different hosts; in real life it's better to have the same MariaDB version throughout your whole environment to avoid compatibility issues.

The next time Puppet catalog runs on the nodes, the repository should be added and the file /etc/yum.repos.d/MariaDB.repo should appear.

Next, you can define MariaDB installation by adding a new Puppet class (named block of Puppet code):

class mariadb {
    package { 'MariaDB-server':
        ensure => installed,
    }
    service { 'mysql':
        ensure => running,
        enable => true,
    }
}

This class instructs the nodes to install the package MariaDB-server. On Red Hat or CentOS nodes it will have the same effect as running the command yum install MariaDB-server. Naturally, it will take care of all the dependencies for MariaDB-server.

After the package directive comes the service one. Notice that I am using the service name mysql, which MariaDB uses to ensure compatibility with MySQL, which it can replace. The service directive ensures that the MariaDB service (mysql) is running, which means that it also ensures that it is started for the first time after the installation. Also, the service is set to enabled, meaning that it will automatically start during the OS boot process.

The only thing left is to include this class in the node's declaration. For example, let's extend an example host1 declaration like this:

node 'host1' {
    class {'mariadbrepo':
        version => '10.0',
    }
    include mariadb
}

After the next Puppet catalog run on the node host1, MariaDB should be installed and started.

MariaDB configuration on the Puppet nodes

To managing the MariaDB environment using Puppet you will probably need to edit some configuration files. For example, if you want to set custom configuration values for the MariaDB server, you will have to edit the file /etc/my.cnf.d/server.cnf on each of the Puppet nodes. You can use Puppet itself to ensure that the configuration is centrally managed and consistent over time and across multiple nodes.

To send a custom configuration file such as /etc/my.cnf.d/server.cnf to your Puppet nodes, create the following resource declaration as part of your mariadb class in the site.pp file:

file { '/etc/my.cnf.d/server.cnf':
      ensure => file,
      mode   => 644,
      source => 'puppet:///conf_files/mariadb/server.cnf',
}

This code specifies the permissions of the file (644) and its source location. Translated, the location puppet:///conf_files/mariadb/server.cnf means /etc/puppet/conf_files/mariadb/server.cnf on the Puppet master.

The above source directive assumes you have configured the Puppet fileserver already. The configuration file /etc/puppet/fileserver.conf should contain the following code:

[conf_files]
   path /etc/puppet/conf_files
   allow *

Furthermore, you should change the service description to recognize the custom MariaDB server configuration file. For this purpose you can use the subscribe metaparameter. Here is how the complete mariadb class should look:

class mariadb {
    package {'MariaDB-server':
        ensure => installed,
    }
    service { 'mysql':
        ensure => running,
        enable => true,
        subscribe => File['/etc/my.cnf.d/server.cnf'],
    }
    file { '/etc/my.cnf.d/server.cnf':
        ensure => file,
        mode   => 644,
        source => 'puppet:///conf_files/mariadb/server.cnf',
    }
}

When you use the subscribe parameter, the MariaDB server will be restarted whenever you make changes to the server configuration file, and thus your changes will take effect immediately.

As you can see, it's easy to install and configure MariaDB with Puppet. You can install different versions and manage configuration files centrally with just a little code and effort.

Tags: 

About the Author

anatoliydimitrov's picture
Anatoliy Dimitrov

Anatoliy Dimitrov is an open source enthusiast with substantial professional experience in databases and web/middleware technologies. He is as interested in technical writing and documentation as in practical work on complex IT projects. His favourite databases are MariaDB (sometimes MySQL) and PostgreSQL. He is currently graduating his master's degree in IT and aims to a PhD in Bionformatics in his home town University of Sofia.


저작자 표시 비영리 동일 조건 변경 허락
신고





Today we're going to cover how to upgrade MySQL 5.1 to MariaDB 10 on Centos 6 in place. This tutorial is a general outline, and the steps were performed on an out-of-the-box install of MySQL 5.1. Do be careful to check your configuration file(s) when completed.

Information on variables and other changes:
http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html
http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
https://mariadb.com/kb/en/mariadb/mariadb-documentation/getting-started/...
https://mariadb.com/kb/en/mariadb/mariadb-documentation/optimization-and...

For this tutorial, we will be following the standard best practice of upgrading to 5.5 first (MariaDB 5.5 in this case). Upgrading directly to MariaDB 10 will fail.

1. Backup

Backup your database(s) with your preferred method. For this tutorial, we'll just use mysqldump. Also be sure to make a copy of your configuration file(s).

[root@upgradeserver /]# mysqldump --all-databases > /tmp/backup.sql
[root@upgradeserver /]# cp /etc/my.cnf /etc/my.cnf.bak

Stop MySQL.

[root@upgradeserver yum.repos.d]# service mysql stop

Make a copy of the data directory just in case.

[root@upgradeserver yum.repos.d]# cp -R /var/lib/mysql /tmp/mysql_backup

2. Configure the MariaDB 5.5 Repository

Use the MariaDB repository configurator to setup a MariaDB 5.5 repo.

https://downloads.mariadb.org/mariadb/repositories/

Copy the output to a new file in /etc/yum.repos.d. I suggest naming it MariaDB55.repo.

[root@upgradeserver yum.repos.d]# cat MariaDB55.repo
# MariaDB 5.5 CentOS repository list - created 2014-04-02 18:03 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Clean the repository cache information.

[root@upgradeserver yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb updates
Cleaning up Everything
Cleaning up list of fastest mirrors

3. Remove MySQL 5.1

Use yum to remove MySQL 5.1.

[root@upgradeserver yum.repos.d]# yum remove mysql-server

4. Install MariaDB 5.5

[root@upgradeserver yum.repos.d]# yum install mysql-server
Loaded plugins: fastestmirror
Determining fastest mirrors
 * base: mirror.thelinuxfix.com
 * extras: mirror.raystedman.net
 * updates: mirror.thelinuxfix.com
base                                                                            | 3.7 kB     00:00
base/primary_db                                                                 | 4.4 MB     00:01
extras                                                                          | 3.4 kB     00:00
extras/primary_db                                                               |  19 kB     00:00
mariadb                                                                         | 1.9 kB     00:00
mariadb/primary_db                                                              |  16 kB     00:00
updates                                                                         | 3.4 kB     00:00
updates/primary_db                                                              | 3.7 MB     00:01
Setting up Install Process
Package mysql-server is obsoleted by MariaDB-server, trying to install MariaDB-server-5.5.38-1.el6.x86_64 instead
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-server.x86_64 0:5.5.38-1.el6 will be installed
--> Processing Dependency: MariaDB-client for package: MariaDB-server-5.5.38-1.el6.x86_64
--> Running transaction check
---> Package MariaDB-client.x86_64 0:5.5.38-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================
 Package                     Arch                Version                    Repository            Size
=======================================================================================================
Installing:
 MariaDB-server              x86_64              5.5.38-1.el6               mariadb               43 M
Installing for dependencies:
 MariaDB-client              x86_64              5.5.38-1.el6               mariadb               10 M

Transaction Summary
=======================================================================================================
Install       2 Package(s)

Total download size: 53 M
Installed size: 217 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): MariaDB-5.5.38-centos6-x86_64-client.rpm                                 |  10 MB     00:15
(2/2): MariaDB-5.5.38-centos6-x86_64-server.rpm                                 |  43 MB     00:25
-------------------------------------------------------------------------------------------------------
Total                                                                  1.3 MB/s |  53 MB     00:40
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : MariaDB-client-5.5.38-1.el6.x86_64                                                  1/2
  Installing : MariaDB-server-5.5.38-1.el6.x86_64                                                  2/2
  Verifying  : MariaDB-server-5.5.38-1.el6.x86_64                                                  1/2
  Verifying  : MariaDB-client-5.5.38-1.el6.x86_64                                                  2/2

Installed:
  MariaDB-server.x86_64 0:5.5.38-1.el6

Dependency Installed:
  MariaDB-client.x86_64 0:5.5.38-1.el6

Complete!

5. Start MySQL

[root@upgradeserver yum.repos.d]# service mysql start
Starting MySQL.. SUCCESS!

6. Run mysql_upgrade

[root@upgradeserver yum.repos.d]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
mysql
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
test
upgrade_test
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK

7. Check Upgrade and Verify Databases

[root@upgradeserver yum.repos.d]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.38-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| upgrade_test       |
+--------------------+
4 rows in set (0.00 sec)

8. Remove MariaDB 5.5

[root@upgradeserver yum.repos.d]# yum remove mysql-server mysql-client
Loaded plugins: fastestmirror
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:5.5.38-1.el6 will be erased
---> Package MariaDB-server.x86_64 0:5.5.38-1.el6 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================
 Package                     Arch                Version                   Repository             Size
=======================================================================================================
Removing:
 MariaDB-client              x86_64              5.5.38-1.el6              @mariadb               43 M
 MariaDB-server              x86_64              5.5.38-1.el6              @mariadb              174 M

Transaction Summary
=======================================================================================================
Remove        2 Package(s)

Installed size: 217 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Erasing    : MariaDB-server-5.5.38-1.el6.x86_64                                                  1/2
  Erasing    : MariaDB-client-5.5.38-1.el6.x86_64                                                  2/2
  Verifying  : MariaDB-server-5.5.38-1.el6.x86_64                                                  1/2
  Verifying  : MariaDB-client-5.5.38-1.el6.x86_64                                                  2/2

Removed:
  MariaDB-client.x86_64 0:5.5.38-1.el6               MariaDB-server.x86_64 0:5.5.38-1.el6

Complete!

9. Disable MariaDB 5.5 Repo

[root@upgradeserver yum.repos.d]# mv MariaDB55.repo MariaDB55.repo.disabled

10. Configure the MariaDB 10 Repository

As we did with the MariaDB 5.5 repository, use the MariaDB repository configurator to setup a MariaDB 10 repo.

https://downloads.mariadb.org/mariadb/repositories/

Copy the output to a new file in /etc/yum.repos.d. I suggest naming it MariaDB10.repo.

[root@upgradeserver /]# cd /etc/yum.repos.d
[root@upgradeserver yum.repos.d]# cat MariaDB10.repo
# MariaDB 10.0 CentOS repository list - created 2014-07-20 22:19 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Clean the repository cache information.

[root@upgradeserver yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb updates
Cleaning up Everything
Cleaning up list of fastest mirrors

11. Install MariaDB 10

[root@upgradeserver yum.repos.d]# yum install mysql-server mysql-client
Loaded plugins: fastestmirror
Determining fastest mirrors
 * base: ftp.linux.ncsu.edu
 * extras: mirror.raystedman.net
 * updates: centos.hostingxtreme.com
base                                                                            | 3.7 kB     00:00
base/primary_db                                                                 | 4.4 MB     00:02
extras                                                                          | 3.4 kB     00:00
extras/primary_db                                                               |  19 kB     00:00
mariadb                                                                         | 1.9 kB     00:00
mariadb/primary_db                                                              |  18 kB     00:00
updates                                                                         | 3.4 kB     00:00
updates/primary_db                                                              | 3.7 MB     00:03
Setting up Install Process
Package mysql-server is obsoleted by MariaDB-server, trying to install MariaDB-server-10.0.12-1.el6.x86_64 instead
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.0.12-1.el6 will be installed
---> Package MariaDB-server.x86_64 0:10.0.12-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================
 Package                     Arch                Version                    Repository            Size
=======================================================================================================
Installing:
 MariaDB-client              x86_64              10.0.12-1.el6              mariadb               11 M
 MariaDB-server              x86_64              10.0.12-1.el6              mariadb               52 M

Transaction Summary
=======================================================================================================
Install       2 Package(s)

Total download size: 63 M
Installed size: 264 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): MariaDB-10.0.12-centos6-x86_64-client.rpm                                |  11 MB     00:07
(2/2): MariaDB-10.0.12-centos6-x86_64-server.rpm                                |  52 MB     00:29
-------------------------------------------------------------------------------------------------------
Total                                                                  1.7 MB/s |  63 MB     00:37
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : MariaDB-client-10.0.12-1.el6.x86_64                                                 1/2
  Installing : MariaDB-server-10.0.12-1.el6.x86_64                                                 2/2
  Verifying  : MariaDB-server-10.0.12-1.el6.x86_64                                                 1/2
  Verifying  : MariaDB-client-10.0.12-1.el6.x86_64                                                 2/2

Installed:
  MariaDB-client.x86_64 0:10.0.12-1.el6              MariaDB-server.x86_64 0:10.0.12-1.el6

Complete!

12. Start MariaDB 10

[root@upgradeserver yum.repos.d]# service mysql start
Starting MySQL. SUCCESS!

13. Run mysql_upgrade

[root@upgradeserver ~]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
ben
information_schema
mysql
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
performance_schema
test
tracker
tracker.accounts                                   OK
tracker.fragment_lookup                            OK
upgrade_test
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK

14. Check Upgrade and Verify Databases

[root@upgradeserver yum.repos.d]# mysql --version
mysql  Ver 15.1 Distrib 10.0.12-MariaDB, for Linux (x86_64) using readline 5.1

[root@upgradeserver yum.repos.d]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.12-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| upgrade_test       |
+--------------------+
5 rows in set (0.01 sec)

15. Configuration File(s)

You'll notice MariaDB breaks the configuration files up.

[root@upgradeserver my.cnf.d]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@upgradeserver my.cnf.d]# ls /etc/my.cnf.d
mysql-clients.cnf  server.cnf  tokudb.cnf

Copy your configurations from the backed-up my.cnf file to the appropriate new .cnf files. Pay special attention to variables which have changed between versions.

Or better yet, use this opportunity to go through the variables and tune your configuration.
https://mariadb.com/kb/en/mariadb/mariadb-documentation/optimization-and...

Tags: 

About the Author

benstillman's picture
Ben Stillman

Ben Stillman is a Senior Consultant working with MariaDB and MySQL.


저작자 표시 비영리 동일 조건 변경 허락
신고





There will come a time when you must search for a particular text string in a field in MariaDB, and you may not know what database or table it might be in. It is somewhat like searching for a needle in a haystack, but fortunately we have good tools for finding our needle. I like to dump the database I'm searching into a text file and do my searches on the file, because it's a fast way to search, and you can mangle the dump file all you want without damaging anything important. When you have the results, you can run SQL queries on the appropriate tables to make the replacements.

As always, be sure you have good backups before you muck around with your MariaDB server. Even better, have a copy of your production server in a sandbox for doing test runs.

First, export your database into a text file, then search the file with your usual text-processing commands (such as grep), or use the search function in your text editor. This command exports the whole works into a single text file:

$ mysqldump -u user -p --extended-insert=false --all-databases  > dbdump.txt

Or you can limit your dump to a specific set of databases:

$ mysqldump -u user -p --extended-insert=false --databases db1 db2 db3 > dbdump.txt

You must use the --extended-insert=false option to format the dump file with line breaks. If you don't, you'll get a mass of data without line breaks, which would be difficult to read. It would also defeat grep; when grep finds a match for your search term, it prints the whole line. No line breaks = big indigestible blob.

This example uses grep to perform a case-insensitive search in a film database for the name Truman:

$ grep -i truman  dbdump.txt 

INSERT INTO `film` VALUES (6,'AGENT TRUMAN');
INSERT INTO `film` VALUES (537,'LOVER TRUMAN');
INSERT INTO `film` VALUES (579,'MINDS TRUMAN');
INSERT INTO `film` VALUES (613,'MYSTIC TRUMAN');
INSERT INTO `film` VALUES (915,'TRUMAN CRAZY');
INSERT INTO `film_text` VALUES (6,'AGENT TRUMAN');
INSERT INTO `film_text` VALUES (537,'LOVER TRUMAN');
INSERT INTO `film_text` VALUES (579,'MINDS TRUMAN');
INSERT INTO `film_text` VALUES (613,'MYSTIC TRUMAN');
INSERT INTO `film_text` VALUES (915,'TRUMAN CRAZY');

And there you go – 10 results, showing which tables they're in: namely "film" and "film_text."

If you dumped multiple databases, how do you know which ones the tables you found belong to? Log into your mysql shell and query information_schema to list all databases and their tables. This example shows how to exclude MariaDB's internal tables:

$ mysql -u root -p
MariaDB [(none)]> SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql');
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| db1          | table1     |
| videos       | film       |   
| videos       | film_text  |
+--------------+------------+

You also need to know the field name that your text string is in, which you can also find withmysql now that you know which database to use:

MariaDB [(none)]> USE videos;
MariaDB [videos]> SHOW COLUMNS FROM film;
+-----------+-------------------------+------+-----+---------+
| Field     | Type                    | Null | Key | Default |
+---------- +-------------------------+------+-----+---------+
| film_id   | smallint(5) unsigned    | NO   | PRI | NULL    |
| title     | varchar(255)            | NO   | MUL | NULL    | 
+-----------+-------------------------+------+-----+---------+

In our search results, fields appear in order between the parentheses, and our search term is the second item, which puts it in the "title" field. Now you can use a SQL query to find "Truman" and replace it with another string – "Wilson," let's say. Mind your cases on your seach and replace terms:

MariaDB [videos]> UPDATE film SET title = REPLACE(title, "TRUMAN", "WILSON");
Query OK, 5 rows affected (0.03 sec)
Rows matched: 1000  Changed: 5  Warnings: 0

Now check your work:

MariaDB [videos]> SELECT title FROM film WHERE title LIKE '%wilson%';

+---------------+
| title         |
+---------------+
| AGENT WILSON  |
| LOVER WILSON  |
| MINDS WILSON  |
| MYSTIC WILSON |
| WILSON CRAZY  |
+---------------+

Well allrighty then, it worked.

What if you want to replace just a subset of your search results, like the first two titles? Adjust your REPLACE syntax to find and replace a match for the whole title:

MariaDB [video]> UPDATE film SET title = REPLACE(title, "AGENT TRUMAN", "AGENT WILSON");

To learn more about fine-tuning your search and replace expressions, consult the REPLACE syntax page and Regular Expressions Overview.

About the Author

carlaschroder's picture
Carla Schroder

Carla Schroder is a system and network administrator who has been riding herd on mixed networks since the last millennium; the author of the Linux Cookbook, the Linux Networking Cookbook, and the Book of Audacity, and hundreds of Linux howtos for various online publications. Official motto: You're never too old to try something new, and anyone can learn to do anything.


저작자 표시 비영리 동일 조건 변경 허락
신고





Introduction

This is an introduction to MariaDB Replication and to why we need a binlogs server and what this is. The first part is an introduction to replication basics, and if you know this already, then you want want to skip past the first section or two.

MariaDB Replication

MySQL and MariaDB has a simple but very effective replication system built into it. The replication system is asynchronous and is based on a pull, instead of a push, system. What this means in short is that the Master keeps track of the DML operations and other things that might change the state of the master database and this is stored in what is called the binlog. The slave on the other hand is responsible for getting the relevant information from the master to keep up to speed. The binlogs consist of a number of files that the master generates, and the traditional way of dealing with slaves is to point them to the master, specifying a starting point in the binlogs consisting of a filename and a position.

When a slave is started it gets the data from the binlogs, one record at the time, from the given position in the master binlogs and in the process updates the current binlogs file and position. So the master keep track of the transactions and the slave follows behind as fast as it can. The slave has two types of threads, the IO thread that gets data from the master and to a separate relay log on the slave, and an SQL thread that applies the data from the relay log to the slave database.

This really is less complicated than it sounds, in a way, but the implementation of it on the other hand is probably more complicated than one might think. There are also some issues with this setup, some which is fixed by the recent GTID implementation in MySQL 5.6 and more significantly in MariaDB 10.

In a simple setup with just 1 master and a few slaves, this is all there is to it: Take a backup from the master and take to keep track of the binlogs position when this is done, then recover this backup to a slave and then set the slave starting position at the position when the backup was executed. Now the slave will catch up with the operations that has happened since the backup was run and eventually it will catch up with the master and then poll for any new events.

One that that is not always the case with all database systems is that the master and the slave are only different in the sense of the configuration. Except this, these servers run the same software and the same operations can be applied to them, so running DML on a slave is no different than on a master, but if there is a collision between some data that was entered manually on the slave and some data the arrives through replication, say there is a duplicate primary key, then replication will stop.

What do we use replication for

Replication is typically used for one or more of three purposes:

  • Read Scale-out - In this case the slaves are used for serving data that is read, whereas all writes go to the master. As in most web applications there is a much larger amount of reads than writes, this makes for good scalability and we have fewer writes to be handled by the single master, whereas the many reads can be server by one or more slaves.
  • Backup - Using a slave for backup is usually a pretty good idea. This allows for cold backups even, as if we shut down the slave for backing it up, it will catch up with the master once restarted after the backup is done. Having a full database setup on a slave for backup also means that recovery times of we need to do that, is fast and also allows for partial recovery if necessary.
  • High Availability - As the master and Slave are kept in sync, at least with some delay, one can sure use the slave to fail over to should the master fail. The asynchronous nature of replication does mean though that failover is a bit more complex than one might think. There might be data in the relay slave log that has not yet been applied and might cause issues when the slave is treated as a master. Also, there might be data in the binlogs on the master which means that when the master is again brought on-line, it might be out of sync with the slave: some data that never was picked up by the slave might be on the master and data that entered the system after the slave was switched to a master is not on the old master.

The nature of replicated data

In the old days, the way replication worked was by just sending any statement that modified data on the master to the slave. This way of working is still available, but over time it was realized that this was a bit difficult, in particular with some storage engines.

This led to the introduction of Row-based Replication (RBR) where the data to be replicated is transferred not as a SQL statement but as a binary representation of the data to be modified.

Replicating the SQL statement is called Statement Based Replication (SBR). An example of a statement that can cause issues when using SBR is:

DELETE FROM test.tab1 WHERE id > 10 LIMIT 5

In this case RBR will work whereas when using statement based replication we cannot determine which rows will be deleted. There are more examples of such non-deterministic SQL statements where SBR fails but RBR works.

A third replication format mode is available, MIXED, where MariaDB decides on a statement by statement base which replication format is best.

Scaling replication

Eventually many users ended up having many slaves attached to that single master. And for a while, this was not a big issue, the asynchronous nature of things means that the load on the master was limited when using replication, but with enough nodes, eventually this turned in to being an issue.

The solution then was to introduce an "intermediate master". This is a slave that is also a master to other slaves, and this is configured having log_slave_updates on, which means that data that is applied on the slave from the relay log and into the slave, are also written to the binlog.

This is a pretty good idea, but there are some issues also. To begin with, on the intermediate master, data has to be written several times, once in the relay log, once in the database (and if InnoDB is used, a transaction log is also written) and then we have to write it to the binlog.

Another issue that is in effect here is the single threaded nature of replication (this is different in MariaDB 10 and MySQL 5.7 and up), which means that a slave on a master that runs many threads, might get into a situation where the slave can't keep up with the master, even though the slave is similarly configured as the master. Also, a run running statement on the master will hold up replication for as long as that statement runs on the slave, and if we have an intermediate master, then the delay will be doubled (once on the intermediate master and once on the actual slave).

The combined effect of the duplication of the delay and the requirement to write data so many times, leads to the result that an intermediate master maybe isn't such a good idea after all.
As for the replication use-cases, intermediate masters are sometimes used as alternative masters when failing over. This might seems like a good idea, but the issue is that the binlogs on the intermediate master doesn't look the same as the binlogs on the actual master. This is fixed by using Global Transaction IDs though, but these have different issues and unless you are running MariaDB 10 or MySQL 5.6, this isn't really an option (and even with MySQL 5.6, there are big issues with this).

What we need then is something else. Something that is a real intermediate master. Something that looks like a slave to the master and as a master to the slave, but doesn't have to write data three times first and that doesn't have to apply all the replication data itself so it doesn't introduce delays into the replication chain.

The slave that attaches to this server should see the same replication files as it would see it it connected to the real master.

MaxScale and the Plugin architecture

So let's introduce MaxScale then, and the plugin architecture. MaxScale has been described before, but one that that might not be fully clear is the role of the plugins. MaxScale relies much more on the plugins that most other architectures, fact is, without the plugins, MaxScale can't do anything, everything is a plugin!


The MaxScale core is a multi-threaded epoll based kernel with 5 different types of plugins (note that there might be more than one plugin of each type, and this is mostly the case actually:

  • Protocols - These implement communication protocols, including debugging and monitoring protocols. From this you realize that without appropriate protocol plugins, MaxScale will not be able to be accessed at all, so these modules are key. Among the current protocols are MariaDB / MySQL Client and Server protocols.
  • Authentication - This type of plugin authenticates users connecting to MaxScale. Currently MariaDB / MySQL Authentication is supported.
  • Router - This is a key type of module that determines how SQL traffic is routed an managed.
  • Filter - This is an optional type of pluging there the SQL traffic can be modfied, checked or rejected.
  • Monitor - This type of modules is there to monitor the servers that MaxScale connects to, and this data is used by the routing mode.

Before we end this discussion on MaxScale, note that there might be several configurations through one single MaxScale setup, so MaxScale can listen to one prot for one set up servers and routine setup, and on another port for a different setup.

With this we have an idea how MaxScale work, so let's see if we can tie it all up.

MaxScale as a Binlog server

As can be seen from the description of MaxScale a lot of what is needed to create a Binlog server to use as an intermediate server for slaves is there. What is needed is a router module that acts as a slave to the assigned master, downloads the binlogs from there, using the usual MariaDB / MySQL Replication protocol. This routing plugin also needs to serve the slaves with the downloaded binlogs files. In theory, and also in practice, the slaves will not know if it is connected to the real master or to MaxScale.

Using MaxScale this way as an intermediate Master, a slave that connects to the MaxScale can work from the same Binlog files and positions as when connected directly to the master, as the files are the same for all intents and purposes. There will be no extra delays for long running SQL statements as these aren't applied on MaxScale, the replication data is just copied from the master, plain and simple. As for parallel slaves, this should work better in when using MaxScale as a Binlog server, but this is yet to be tested.

So there should be many advantages to using MaxScale as a binlog server compared to using an intermediate MariaDB / MySQL server. On the other hand, this solution is not for everyone, many just don't drive replication so hard, that the load on the master is an issue so that an intermediate Master is requited. On the other, many use an Intermediate Master also for HA, and in this case it would have be advantageous to use MaxScale instead of that Intermediate master, the latter which could still server the role as a fail-over HA server.

Now, there isa one issue with all of this that many of you might have spotted: That cool Binlog server plugin module for MaxScale doesn't exists. Well, I am happy to say that you are wrong, it does exist and it works. A pilot for such a module has been developed by SkySQL together with a customer that had just this need for an intermediate server that wasn't just yet another MariaDB / MySQL server.

About the Author

anderskarlsson's picture
Anders Karlsson

Anders Karlsson is a Sales Engineer with a long experience from the field of database software. Anders has worked for many of the major database software companies.


저작자 표시 비영리 동일 조건 변경 허락
신고






You've decided to move from MySQL to MariaDB, but you're a little nervous. The last thing you want is to become mired in a slough of brokenness. Don't worry, because MariaDB is designed to be a drop-in replacement for MySQL. You should be able to install MariaDB over your MySQL server, and then go about your business without drama.

The simplest method follows these steps:

  1. Update your software repositories list with the MariaDB repos
  2. Update your Linux package manager with the new repos
  3. Stop MySQL
  4. Install MariaDB with your package manager
  5. Go back to work because you're done

Before you do anything, make sure you have current backups (which you should have anyway), and do the migration during a quiet time when you can monkey around with minimal upset to users. A good confidence-builder is to replicate your production server on a testing sandbox, and then practice on it. You'll want MariaDB to be the same or higher version than your MySQL server. MySQL major releases are 5.0, 5.1, 5.5, and 5.6. MariaDB versions are 5.1, 5.2, 5.3, 5.5, and 10. You can reliably switch to MariaDB and then switch back to MySQL, if you wish, up to 5.5, but after that they diverge enough that I consider moving to MariaDB a one-way trip.

The nice people at MariaDB have created a copy-and-paste installation guide and wizard for the major Linux distributions. Choose your distro, MariaDB version, and download mirror, then copy and run the command created by the wizard to install the repository signing key, and copy the repository configuration into your sources list. Run your package manager's update command, stop your MySQL server, and then install MariaDB with your package manager. The installation guide thoughtfully includes all the relevant commands for you. Your package manager should automatically run mysql_upgrade, which is a necessary step to check all of your tables for compatibility with the current version of MariaDB, and fix anything that isn't right. If it doesn't, runmysql_upgrade manually. Then restart MariaDB, check your logfiles, and if no errors appear, you're done.

Another, and more labor-intensive, option is to install the new MariaDB server alongside your existing MySQL installation, then migrate databases and applications one at a time. To do this, download the MariaDB source tarball; compile and install the binary; create users, directories, and the init file; set permissions; start MariaDB; check that both MySQL and MariaDB are running; and then start moving stuff. This is also a good exercise for a refresher in the nuts and bolts of installation and configuration, which is always good to know and could save the day when something doesn't work right.

If you're running a more complex setup with tons of tables and databases and clustering and masters and slaves, take a look at this detailed guide by Sheeri K. Cabral of the Mozilla IT and Operations team. These people are so proficient with MySQL and MariaDB that they switch between them to take advantage of specific features in new releases. They start by making a logical backup of the database on a slave server, then import this backup into a new empty server installation. Before starting the export/import they use checksums to verify that the data are consistent between master and slave, issue maintenance notices, prevent anyone but root from logging in, take the server out of the load balancer, make sure the slave does not get overwritten by the master during the export, and perform a number of other checks and tests. It's a lot of steps, but it's a good safe method that allows you to take your time without taking everything offline.

Resources

About the Author

carlaschroder's picture
Carla Schroder

Carla Schroder is a system and network administrator who has been riding herd on mixed networks since the last millennium; the author of the Linux Cookbook, the Linux Networking Cookbook, and the Book of Audacity, and hundreds of Linux howtos for various online publications. Official motto: You're never too old to try something new, and anyone can learn to do anything.


저작자 표시 비영리 동일 조건 변경 허락
신고





MariaDB ?

MySQL의 쌍둥이 형제인 MariaDB는  MySQL의 발전된 형태의 오픈소스 데이터베이스로써, GPL v2 라이센스가 유지되고 있으며, MariaDB 커뮤니티와 Maria 재단이 주축이 되어서 개발을 하고 있습니다.

MySQL과 동일한 소스 코드를 기본으로 하며, MySQL과 높은 호환성을 유지하고 있습니다.

Maria DB는 새로운 저장 엔진인 아리아(Aria)와 함게, InnoDB를 교체할 수 있는 XtraDB 저장 엔진을 포함하고 있습니다.

Maria DB의 주요 개발자는 MySQL과 몬티 프로그램 AB를 설립한 마이클 몬티 와이드니어스(Michael Monty Widenius)입니다

그는 MySQL을 썬마이크로시스템즈에 판매했으며, 이후 Oracle은 썬마이크로시스템즈를 M&A함과 동시에 MySQL은 Oracle의 소유로 넘어가게 되었습니다. 

그는 퇴사 후 동료들과 함께, Mysql 판에 대응하고자 그의 둘째 딸인 마리아의 이름을 따 새로 Maria DB를 개발하였습니다.



MariaDB vs MySQL ?

MariaDB 는 현재까지 최신의 MaySQL 과 같은 브랜치로부터 릴리즈되며, 동일한 소스코드를 기반으로 함으로써 대개의 경우 MySQL과 동일한 형태로 동작합니다.

MySQL의 모든 명령어, 인터페이스, 라이브러리와 API 가 MariaDB 에도 존재합니다. 또한 MariaDB 로 데이터베이스를 변환할 필요도 없습니다. 더군다나, MariaDB는 MySQL보다 더욱 새로운 기능들을 포함하고 있습니다.

근본적인 차이점이라고 하면 MariaDB는 GPL v2 라이선스를 따르는 순수 오픈소스이며, MySQL은 Oracle에 종속되어 있습니다.

다시 말해, 누구나 필요로 하면 커뮤니티를 통해 MariaDB를 접할 수 있습니다.



MariaDB 의 기능?

MariaDB 커뮤니티는 MySQL과 비교해 리플리케이션 부분 속도가 약 4~5천배 정도 빠르며, MySQL이 가지고 있는 모든 제품의 기능을 완벽히 구현하면서도 성능 면에서는 최고 70%의 향상을 보이고 있다고 주장하고 있습니다.



MariaDB 의 엔진?

그리고, MariaDB는 MySQL과 거의 동일한 데이터베이스 엔진(스토리지 엔진)에 대응하고 있습니다.

MariaDB의 엔진은 아래와 같습니다.


- Aria - MyISAM 파생 엔진 대체용 (이전 명칭은 Maria)

- XtraDB - 오라클 InnoDB를 대체하기 위해 만든 InnoDB 파생 포크

- FederatedX - MySQL Federated 파생 엔진, 트랜잭션 제공

- OQGRAPH - 버전 5.2 이상에서 지원

- SphinxSE - 버전 5.2 이상에서 지원, Full-Text Searching이 필요할 때 사용할 수 있는 스토리지 엔진

- IBMDB21 - 오라클은 이것을 MySQL 5.1.55에서 제거했지만, 마리아DB에서는 5.5까지 유지

- Cassandra - 10.0에서 포함. 기타 비 sql 저장 엔진을 끌어들이려는 시도

- PBXT - 트랜잭션 제공, 5.5부터는 더 이상 유지보수를 제공하지 않음. 기본 스토리지 엔진에서 제외

- TokuDB - 트랜잭션 제공, 프랙탈 트리(Fractal Tree) 인덱스 지원. 버전 10부터 기본 스토리지 엔진


현재 Maraidb는 MariaDB 와 MySQL 은 모두 MariaDB 커뮤니티에 의해 잘 지원되고 있습니다.

한편, Monty Program Ab SkySQL 를 포함한 여러 회사들은 완벽한 24x7 컨설팅, 오류 수정 등의 차별화된 지원을 제공하고 있습니다.

저작자 표시 비영리 동일 조건 변경 허락
신고