Still running MySQL 5.5 on your cPanel & WHM server? It’s important to know that this version of MySQL has been in its end-of-life (EOL) status since December, 2018. As a result, no more security, bug fixes, or updates will be available for servers that are still running MySQL 5.5. Major control panel providers already started to drop the support for MySQL 5.5 as well. That said, time has come to upgrade to a newer version of MySQL or MariaDB.

cPanel & WHM also announced that they will block any panel updates for servers running MySQL 5.5 from version 80. So, that is another good reason not to stick to the old version of MySQL.

To Which Version Should MySQL 5.5 be Upgraded?

Considering that MySQL 5.6 will be in its EOL status by February, 2021, it may be better to upgrade to MySQL 5.7 or MariaDB 10.3. MySQL 5.6 will only last for a few years; hence, upgrading to MySQL 5.7 is strongly recommended. This will ensure that you can enjoy the new and improved features that come with version 5.7, even before its predecessor reaches its end of life.

How to Upgrade Your MySQL

Migrating from MySQL 5.5 to a newer version is a major upgrade that must be done with some consideration in mind. Returning to MySQL 5.5, in case anything goes wrong, is not possible, so a solid backup is essential before you can proceed with the upgrade. For better stability, we recommend a systematic upgrading approach. Here is the step-by-step procedure for upgrading your MySQL:


1 – Log into WHM, go to “SQL Services“, and click on “MySQL/MariaDB Upgrade“.


2 – Confirm your current MySQL version, select the new version you want to upgrade to, and then click “Next“. Upgrade one version at a time.


3 – On the next interface, check the boxes for each warning to acknowledge that you are aware of the potential consequences of the upgrade, then click “Continue“. It’s important to know that downgrading from MariaDB is not supported by cPanel & WHM or switching back to MySQL. It’s also not recommended to try doing so. This must be considered when upgrading from MySQL to MariaDB.


4 – Choose the upgrade type you wish to use:

Unattended Upgrade: (Recommended) — Automatically rebuilds Apache and PHP with the last saved defaults. It also automatically updates Ruby Gems and rebuilds Apache and PHP.

Partially-Interactive Upgrade: (On some cPanel & WHM versions only) — This option automatically updates Ruby Gems, but it does not automatically rebuild Apache and PHP, until you choose to do so.

Interactive Upgrade: (Advanced users only) — This option takes you through the upgrade process step by step. The process includes the Ruby Gems update, the MySQL or MariaDB upgrade, and the Apache and PHP rebuild.

Once you have selected the upgrade type, click “Continue” to start the upgrade process.

After the MySQL Upgrade

Once the upgrade is complete, we strongly suggest that you check your website databases just to be sure that things are working properly. Check the MySQL error log for any errors. The best way to check your websites for errors is by testing them in a browser. In some cases, you may need to update the user password for a specific database, due to an incompatibility issue. Updating the password is a quick fix.

If your error log is not customized, your configuration file is usually named $hostname.err in /var/lib/mysql/. If your hostname, for instance, is ‘host.example.com’, the file will be named host.example.com.err. To interact with this file, you could use commands such as cat and tail: tail /var/lib/mysql/`hostname`.err. The file should not be edited.

Once you confirm that all your sites are working properly, the migration is complete.

How to Install MongoDB on Debian 9

MongoDB is a free, open-source document-oriented database, which is classified as a NoSQL database; NoSQL databases are not relational databases but are based on other data storage and retrieval means. Unlike other database programs, such as MySQL and MariaDB, MongoDB is based on JSON-like documents that are flexible, which means fields in different documents can vary and the data structure can change over time. MongoDB also provides users with high availability, smooth scaling, and simple geographic distribution, making it an efficiently utilized solution.

install mongodb

MongoDB was released in February of 2009 and is made available for free use while offering commercial support and other services to consumers.

Getting Started

To install MongoDB on Debian 9, sometimes called Debian Stretch, you will need one node that is running the Linux Debian 9 operating system; the node you choose may be a cloud server node or dedicated server node. The operating system needs to be up to date, and you will need root access to your server.

Root access normally requires a root username and root password, and this allows you to execute commands with higher levels of authority than a standard user would have access to execute.

How to Install MongoDB on Debian 9

The first thing to remember is that each command executed below is executed with root user access.

First, it’s essential to update the server before attempting to install MongoDB:
apt-get update && apt-get upgrade -y

When the server is finishing updating, it’s time to install MongoDB:
apt-get install mongodb

Once MongoDB is installed, you will need to start and stop MongoDB, verifying that it functions correctly:
systemctl start mongodb
systemctl stop mongodb

Now you can check your MongoDB version, making sure it’s the proper version or the version you were expecting:
mongod --version
db version v3.2.11
git version: 009580ad490190ba33d1c6253ebd8d91808923e4
OpenSSL version: OpenSSL 1.0.2l 25 May 2017
allocator: tcmalloc
modules: none
build environment:
distarch: x86_64
target_arch: x86_64

After completing the installation and verifying the MongoDB version, it’s time to test MongoDB, making sure it functions as anticipated on your server:
mongo
MongoDB shell version: 3.2.11
connecting to: test
> help
db.help() help on db methods
db.mycoll.help() help on collection methods
sh.help() sharding helpers
rs.help() replica set helpers
help admin administrative help
help connect connecting to a db help
help keys key shortcuts
help misc misc things to know
help mr mapreduce
show dbs show database names
show collections show collections in current database
show users show users in current database
show profile show most recent system.profile entries with time >= 1ms
show logs show the accessible logger names
show log [name] prints out the last segment of log in memory, 'global' is default
use set current database
db.foo.find() list objects in collection foo
db.foo.find( { a : 1 } ) list objects in foo where a == 1
it result of the last line evaluated; use to further iterate
DBQuery.shellBatchSize = x set default number of items to display on shell
exit quit the mongo shell
> exit
bye

Conclusion

Congratulations, you’ve completed installing MongoDB on Linux Debian 9, and it’s time to move your server into production. If you found this guide helpful, please share it with other users that are interested in setting up the same document-oriented database system.

How to Backup MySQL Databases

MySQL is an open-source relational database management system and has grown to be one of the most popular database systems online. MySQL is implemented in a variety of solutions, including WordPress and Joomla, as well as high-profile websites, such as Facebook and Twitter. MySQL offers a variety of helpful tools to the user, though there are risks to tying so much information into a relational database. For example, there are several applications that rely on the database to function, becoming useless without the data within MySQL to function. The importance of these systems is why backing up the MySQL database is essential.

MySQL Databases

Getting Started

To get started with MySQL databases backup, you will need to have a MySQL database running as well as access to the MySQL root username and password. Additionally, you should assess how many databases there are that need to be backed up. There are command line functions available for a single database, multiple databases, or every MySQL database.

Backing Up MySQL Databases

The first step to backing up the database is to get access to the MySQL root username and password, as mentioned before.

Once you have that access, the next step is determining how many databases need to be backed up.

If you are backing up one database, you can use the following command:
mysqldump -u root -p database_name > database.sql

If you are backing up multiple databases, the followed command should be used:
mysqldump -u root -p --databases database_one database_two > both_databases.sql

There are instances where you may want to backup every MySQL database. When that’s the case, the following command can be used:
mysqldump -u root -p --all-databases > all_databases.sql

You may find that the backups of the database are larger than expected. If this is the case, or you need to save on storage space, you can backup and compress a database using the following command:
mysqldump -u root -p database_name | gzip -9 > backup_db.sql.gz

Conclusion

Congratulations, you’ve successfully completed a backup of your MySQL databases using the command line. It’s important to implement these types of backups frequently, ensuring that any crucial operation data is saved and secured, just in case there’s a data breach, data is compromised, or there’s an issue with the database. If you found this guide helpful, please share it with others engaged in the same process.

How to Install MySQL 5.7 on Debian 9

Debian 9 is the latest stable release in the Debian operating system line, which is an open source project based on Unix systems. The current release, given the codename Stretch, has several changes that impact user functionality and third-party application compatibility. Each major Debian release provides new packages, software updates, and comes with three years of support for the release; stable releases, such as Stretch, are released every two years.

Debian 9

However, one major change from the previous Debian 8 release is the change from MySQL to MariaDB. With the new release, the previous instances of MySQL are being replaced by the equivalent instance of MariaDB. This guide will help you install MySQL 5.7 on a node running the Debian Stretch operating system.

Getting Started

To get started with installing the MySQL packages on Stretch, you need to have a cloud server or dedicated server node, whichever you prefer, with an current instance of Debian Stretch installed.

Mentioned earlier, one of the biggest changes from the previous stable release to this release was the replacement of MySQL with MariaDB; MySQL was the default. However, it’s possible to install MySQL as the default database, replacing MariaDB, with an official MySQL repository.

During this process, having root user access, sometimes called privileged access, may be necessary to complete the setup. You will be prompted to create a MySQL root password during installation as well.

Installing MySQL 5.7 on Debian 9

The first step in installing the official MySQL instance is to go to MySQL’s website, find the correct .deb package, and download the .deb package.

Now that the .deb package is downloaded, you will need to download it to your Debian server, following the below example:
wget https://dev.mysql.com/get/mysql-apt-config_0.8.8-1_all.deb

Once downloaded to your server, check the .deb package name, then install it with dpkg:
ls mysql-apt-config_0.8.8-1_all.deb

Follow the steps to complete the download of your .deb package:
dpkg -i mysql-apt-config_0.8.8-1_all.deb

During the installation, you will be prompted to select options for your install. Make sure to leave the default options in place, then click “OK”

After the installation is complete, now you can update the package list:
apt update

When the above steps have completed, you are now ready to proceed with installing MySQL, both the server and the client. You will also receive a prompt, during setup, to create a MySQL root user password:
apt install mysql-server mysql-client

Once the installation of the MySQL server and MySQL client have completed, you need to enable the MySQL instance on your server, then start MySQL:
systemctl enable mysql && systemctl start mysql

Securing your MySQL installation is essential, so run the below script, leaving the default settings alone, to secure your MySQL server and client:
mysql_secure_install

Conclusion

Congratulations! You’ve completed an install of MySQL 5.7 on your Debian 9 cloud server or dedicated server node. While installing this official MySQL instance, you’ve replaced the existing MariaDB instance; this makes MySQL the default database software. If you had success using this guide to update your current Debian release with the MySQL instance, please share it with others going through the same process.

The MySQL relational database management system is highly configurable. Once you have installed MySQL on your Linux server there are an enormous number of ways that you may tune it to suit your needs. This present tutorial is designed to introduce you to the basics of MySQL tuning by taking you through the deployment and execution of a few tuning scripts. This tutorial should be applicable to any variety of Linux server.

Tutorial

In order to tune your MySQL instance exactly as you want it you need to know what all the MySQL configuration variables are and what they do. However, there are a few scripts out there that will enable you to accomplish a fair bit of tuning without such extensive knowledge. We will show you how to use two of the most widely used tuning scripts to get you started. Once we have shown you the basics, you will be ready to learn more by delving into the manual tuning sections of the official documentation for MySQL official documentation.

Before You Tune

Tuning scripts are typically designed to provide you with recommendations for how to tune your MySQL instance based on how MySQL is used on your server. Therefore, it is imperative that you only run tuning scripts when the MySQL instance has been up and running for at least the previous 24 hours. Furthermore, the traffic from the previous 24 hours needs to have been typical of the traffic on the server. Assuming you have had normal traffic, the recommended sampling time should provide the scripts with enough reliable data on your MySQL instance to make recommendations that will make your configuration serve your needs better.

As you may have guessed, tuning is not generally effective in development environments. Tuning is designed to take advantage of the use metrics in your production environment to drive changes in the configuration of that instance. The development environment simply will not see such traffic.

Downloading the scripts

Download both tuning scripts. These can be placed in whichever folder you wish. We’ll use /root for convenience.

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
wget https://launchpadlibrarian.net/78745738/tuning-primer.sh

Make the scripts executable with the following command:

chmod +x mysqltuner.pl
chmod +x tuning-primer.sh

Executing the Scripts

Once the scripts are in place, we are ready to execute them.

The first script (mysqltuner.pl) is designed to provide recommendations. This script is especially useful for beginners. The second script (tuning-primer.sh) simply provides data. The latter is recommended for advanced users, as the data it provides will point such users to the configuration parameters they need to change in order to optimize their setup.

Run mysqltuner.pl

root@server [~]# ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.52-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 591M (Tables: 339)
[--] Data in InnoDB tables: 7G (Tables: 3641)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 136)
[!!] Total fragmented tables: 467
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 11h 54m 43s (526M q [1K qps], 1M conn, TX: 364B, RX: 235B)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 2.1G global + 6.5M per thread (512 max threads)
[OK] Maximum possible memory usage: 5.3G (34% of installed RAM)
[OK] Slow queries: 0% (433/526M)
[OK] Highest usage of available connections: 12% (63/512)
[OK] Key buffer size / total MyISAM indexes: 32.0M/261.5M
[OK] Key buffer hit rate: 100.0% (418M cached / 178K reads)
[OK] Query cache efficiency: 83.5% (419M cached / 502M selects)
[!!] Query cache prunes per day: 15833584
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 30M sorts)
[!!] Joins performed without indexes: 145541
[!!] Temporary tables created on disk: 32% (7M on disk / 21M total)
[OK] Thread cache hit rate: 99% (2K created / 1M connections)
[!!] Table cache hit rate: 1% (5K open / 329K opened)
[OK] Open file limit used: 1% (866/50K)
[OK] Table locks acquired immediately: 99% (185M immediate / 185M locks)
[!!] InnoDB data size / buffer pool: 7.7G/2.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 6000)
innodb_buffer_pool_size (>= 7G)

In our example, the output of the script indicates we should increase the value of a few parameters in our primary MySQL configuration file, my.cnf.

We can edit the file and add/modify the corresponding entries like so:

nano /etc/my.cnf

The updated file looks like this:
max_connections = 512
query_cache_size = 32M
query_cache_limit = 4M
thread_cache_size = 8
table_cache = 12000
table_definition_cache = 6000
open_files_limit = 50000
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
key_buffer_size = 32M
innodb_buffer_pool_size = 2048M
max_allowed_packet=268435456
default-storage-engine=MyISAM

In our example, we had already applied some changes. We’ll modify the corresponding entries according to the proposed tweaks.

The script is not perfect. Some of its recommendations are counterproductive. For instance, the script will often incorrectly indicate that the join_buffer_size needs to be raised in response to the execution of non-indexed join inquiries. Regardless of the scripts output, we have found that keeping the join_buffer_size under 4M consistently helps maximize performance. We’ll set it at 3M here.

Another parameter the script often recommends increasing is the innodb_buffer_pool_size. When increasing this, make sure you do not set it such that you exceed the RAM memory available on your server. If you do so, your server will probably crash when the buffer pool size approaches its maximum. In our scenario, our system has about 2 to 3GB of usable RAM. To be safe, we will set the innodb buffer pool to be about half of that, or 1024 M. If the script recommends you increase this buffer pool size beyond what your server can handle, we would recommend you add more RAM so that your server can meet your needs.

query_cache_size = 48M
join_buffer_size = 3M
tmp_table_size = 32M
max_heap_table_size = 32M
table_cache = 12000
innodb_buffer_pool_size = 3096M

After making the adjustments described above, the tweak section of our my.cnf file should look as follows:

max_connections = 512
query_cache_size = 48M
query_cache_limit = 4M
thread_cache_size = 8
table_cache = 12000
max_heap_table_size = 32M
tmp_table_size = 32M
table_definition_cache = 12000
open_files_limit = 50000
join_buffer_size = 3M
read_buffer_size = 2M
sort_buffer_size = 2M
key_buffer_size = 32M
innodb_buffer_pool_size = 3096M
max_allowed_packet=268435456
default-storage-engine=MyISAM

Next, we will run tuning-primer.sh using the command below. Do this before you restart the mysql daemon. This script will provide us data that will help us to know if the tweaks we intend to apply are likely to help. In this scenario, the second script really just serves to confirm what the first script said.

root@server [~]# ./tuning-primer.sh

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.5.52-cll x86_64
Uptime = 4 days 12 hrs 5 min 53 sec
Avg. qps = 1353
Total Questions = 526850500
Threads Connected = 13


Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service


SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 433 out of 526850789 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine


BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html


WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 2
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine


MAX CONNECTIONS
Current max_connections = 512
Current threads_connected = 14
Historic max_used_connections = 63
The number of used connections is 12% of the configured maximum.
Your max_connections variable seems to be fine.


INNODB STATUS
Current InnoDB index space = 6.44 G
Current InnoDB data space = 7.70 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 2.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory


MEMORY USAGE
Max Memory Ever Allocated : 2.47 G
Configured Max Per-thread Buffers : 3.25 G
Configured Max Global Buffers : 2.07 G
Configured Max Memory Limit : 5.32 G
Physical Memory : 15.53 G
Max memory limit seem to be within acceptable norms


KEY BUFFER
Current MyISAM index space = 261 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 2345
Key buffer free ratio = 25 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere


QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 22 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 71.32 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size


SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine


JOINS
Current join_buffer_size = 2.00 M
You have had 145555 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.


OPEN FILES LIMIT
Current open_files_limit = 50000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine


TABLE CACHE
Current table_open_cache = 6000 tables
Current table_definition_cache = 6000 tables
You have a total of 4157 tables
You have 5857 open tables.
Current table_cache hit rate is 1%
, while 97% of your table cache is in use
You should probably increase your table_cache


TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 14447005 temp tables, 32% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.


TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 1002 : 1
read_buffer_size seems to be fine


TABLE LOCKING
Current Lock Wait ratio = 1 : 31200
Your table locking seems to be fine

This output indicates that the tweaks made in response to mysqltuner.pl are likely to be beneficial.

The tuning-primer.sh script shows that the table cache was getting full. An increase will help. The same is true of our indoor buffer pool. Recall that we had raised the join_buffer_size in response to the large amount of join queries without indexes; however the present script indicates, as we predicted, that such an increase will not address the performance issue. Rather, we will need to add indexes to the join queries in question.

With the results of the two scripts, we can be confident that the recommended tweaks will improve the performance of our MySQL instance.

Restart your MySQL instance

To apply the tweaks made the MySQL configuration file (/etc/my.cnf), restart the mysql daemon.

root@server [~]# service mysql restart
Shutting down MySQL..... [ OK ]
Starting MySQL.. [ OK ]
root@server [~]#

Your MySQL instance has been tuned. Going forward, further tuning may be necessary if there are changes in server traffic related to your databases. If you conclude that the performance of your MySQL instance is still not what you want it to be, let your server run for 24 to 48 hours and run the scripts again. We suggest you repeat this process until you have no further recommendations to apply on your server.

Conclusion

Your MySQL instance should now perform better than ever. With the knowledge you have gained here, you are ready to explore further tuning using the official MySQL documentation.

MySQL 101 – The basics

MySQL is one of the most widely used relational database management systems (RDBMS). MySQL is used to manage databases in a wide variety of applications including the integrated web solution known as LAMP (Linux Apache MySQL Perl/PHP/Python). Database management is accomplished in MySQL using Structured Query Language (SQL). Because it scales well, MySQL works for projects of almost any size and complexity. Strong security features and the abundance of tools to control it make MySQL attractive to users who want to protect their data and still have easy access to and easy use of it.

Tutorial

This guide has been created to serve as a reference for most basic commands in SQL. Below, we have used a random test database to show you the basic principles of operating MySQL directly from the Command Line Interface (CLI). If you master the commands below, you will be well on your way to understanding how to effectively manage a database using MySQL.

Connecting to MySQL

To get started, establish an active SSH session on your server. We will connect using the root user of our MySQL instance for convenience. To follow along, you will need to know your MySQL root password to initiate your session.

mysql -u root -p

Password:


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5975
Server version: 5.1.73 Source distribution


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


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


mysql>

The appearance of the mysql> prompt after you enter the command to initiate the session indicates you are connected to the MySQL instance on your server and can execute commands.

View databases

Our first command is SHOW DATABASES. When you enter this command, a list of all the active databases on this MySQL instance will print to the screen.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| radius |
+--------------------+
3 rows in set (0.02 sec)

Create a database

To use MySQL for your own projects you will need your own databases. Whether you want to start from scratch, or import a schema or data from an existing database you will need first to create the databases you want to use in this instance of MySQL. Go ahead and create the database “mynewdb” with the following command:

mysql> create database mynewdb;
Query OK, 1 row affected (0.00 sec)

Your database “mynewdb” is now ready to use. Now, you can create tables in it or import the contents of an existing database or schema.

Use a database

The USE command will enable you to select a specific database to work on. For instance, you may use mynewdb by entering

[root@server ~]# mysql -u root -p mynewdb < radius.sql
Enter password:

Populating a database

You have two basic options for populating mynewdb: Manually create tables and insert data into those tables or import the contents of an existing database or schema structure into mynewdb. Because importing is more straightforward, we will start there.

Import data

To import data from an existing database, you must execute the command outside of MySQL, directly in the SSH command line. MySQL database backups are text files that are typically created with the .sql file extension name so that they may be easily identified as database backups. For our example, we'll import the contents of the radius.sql file into the mynewdb database.

mysql> use radius
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

The IMPORT command populated mynewdb by creating a copy of all of the tables and records that were found in the original database. MySQL accomplished this duplication by running every SQL command that had been run to populate the original database.

Delete a database

When a database has reached the end of its useful life deleting it is quite a straightforward process. Simply do as we show below to remove a database from your MySQL instance.

mysql> drop database mynewdb;
Query OK, 35 rows affected (0.05 sec)

Be careful with this command. Once a database is deleted the data in it cannot be recovered from within MySQL. Of course, if you have retained a backup you can always create a new database and import the contents of the old one.

Create a table

MySQL databases are built using tables. The creation a MySQL table is a complex task. Tables are so customizable that they really need to be designed to meet your specific needs or the needs of your application. To keep this guide brief we'll show you how we created a specific table we'll be using in our further examples below.

One of the tables in our sample database was created like so:

mysql> CREATE TABLE `radgroupreply` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`attribute` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`op` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '=',
`value` varchar(253) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `groupname` (`groupname`(32))
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.06 sec)

Show tables within the database

Tables are associated with the database in which they are created. To see a list of all the tables in the database with which you are working use the SHOW TABLES command:

mysql> show tables;
+------------------------+
| Tables_in_radius |
+------------------------+
| batch_history |
| billing_history |
| billing_merchant |
| billing_paypal |
| billing_plans |
| billing_plans_profiles |
| billing_rates |
| cui |
| dictionary |
| hotspots |
| invoice |
| invoice_items |
| invoice_status |
| invoice_type |
| nas |
| node |
| operators |
| operators_acl |
| operators_acl_files |
| payment |
| payment_type |
| proxys |
| radacct |
| radcheck |
| radgroupcheck |
| radgroupreply |
| radhuntgroup |
| radippool |
| radpostauth |
| radreply |
| radusergroup |
| realms |
| userbillinfo |
| userinfo |
| wimax |
+------------------------+
35 rows in set (0.01 sec)

If you are following along with our guide here, you should see the newly created table in your list.

Delete a table

Occasionally, you realize you don’t need a table anymore. Simply follow the command below to eliminate the unwanted table.

mysql> drop table radgroupreply;
Query OK, 0 rows affected (0.01 sec)

As with the delete database command, you should use this command with caution as it is not easy to recover from the mistake of using it when you don’t mean to do so.

Show the structure of a table

During the lifetime of your database, you will likely want to add (INSERT) or modify (UPDATE) fields. To use both the INSERT and UPDATE commands effectively you need to know what is already in the database. To list the existing columns, rows, and all their associated parameters use the DESCRIBE command:

mysql> desc wimax;
+----------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | | |
| authdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| spi | varchar(16) | NO | MUL | | |
| mipkey | varchar(400) | NO | | | |
| lifetime | int(12) | YES | | NULL | |
+----------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

Select rows from a table

Use the SELECT command to view a particular portion of your data in a table (or all of it). This command is highly customizable. We'll show you a few variants below.

Select everything

If you wish to generate a list of everything from the table enter the following.

mysql> SELECT * FROM radgroupreply;
+----+-----------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 5 |
| 3 | test2 | This is a second test | = | 29 |
| 4 | test3 | This is a third test | = | 12 |
+----+-----------+-----------------------+----+-------+

Select everything where groupname is default

Groupname is the title of a column or field in our table. Use of the command

mysql> SELECT * FROM radgroupreply WHERE groupname="default";
+----+-----------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
+----+-----------+-----------------------+----+-------+
1 row in set (0.00 sec)

will return the whole row where the value of the entry in the column groupname matches the "default" value.

Select everything where value is smaller or equal to 15

The following will return the whole row where the value field is populated with a value less than or equal to 15 (<=). mysql> SELECT * FROM radgroupreply WHERE value<=15;

+----+-----------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 5 |
| 4 | test3 | This is a third test | = | 12 |
+----+-----------+-----------------------+----+-------+
3 rows in set (0.00 sec)

You may construct similar commands with other operators such as <, >, <=, >= and =.

Select the data from only 1 field for all rows

In this example, we will select only one field from the table in question.

mysql> SELECT value FROM radgroupreply;
+-------+
| value |
+-------+
| 15 |
| 5 |
| 29 |
| 12 |
+-------+
4 rows in set (0.00 sec)

The data is displayed in a single column rather than being formatted like the whole table.

Select the data from only multiple fields for all rows

Here we will select multiple fields from the table in question. You'll see that the data is displayed in two columns.

mysql> SELECT groupname,value FROM radgroupreply;
+-----------+-------+
| groupname | value |
+-----------+-------+
| default | 15 |
| test1 | 5 |
| test2 | 29 |
| test3 | 12 |
+-----------+-------+
4 rows in set (0.00 sec)

Following the pattern established above, you can select the data from as many or as few of the fields in a table as you wish.

INSERT Rows in a Table

The INSERT command enables you to insert rows into a specific table. Inserts can be performed in a few different manners. The method you choose will depend on the mechanics of your code. We will show you two common methods to give you a general sense for how it is done.

First insert method

Our first example of how to use INSERT is based on our previous examples of how to use SELECT. This command creates a row consistent with the same table format used above:

mysql> INSERT INTO radgroupreply (groupname, attribute, op, value) VALUES ('my group is the best', 'this attribute rocks', '=', '12');
Query OK, 1 row affected (0.00 sec)

To check that the row has been inserted, we can perform a select on radgroupreply. The command and results should appear as follows:

mysql> SELECT * FROM radgroupreply;
+----+----------------------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+----------------------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 5 |
| 3 | test2 | This is a second test | = | 29 |
| 4 | test3 | This is a third test | = | 12 |
| 5 | my group is the best | this attribute rocks | = | 12 |
+----+----------------------+-----------------------+----+-------+
5 rows in set (0.00 sec)


Second insert method

A new method of inserting data into MySQL tables has gained popularity recently. This method achieves the same thing as the first but the formatting of the query is quite a bit different, as you can see below:

mysql> INSERT INTO radgroupreply SET groupname='This other group rocks', attribute='Second type of insert method', op='+', value='11';
Query OK, 1 row affected (0.00 sec)

As with the previous INSERT method, to confirm the insertion worked properly, perform a select on radgroupreply as follows:

mysql> select * from radgroupreply;
+----+------------------------+------------------------------+----+-------+
| id | groupname | attribute | op | value |
+----+------------------------+------------------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 5 |
| 3 | test2 | This is a second test | = | 29 |
| 4 | test3 | This is a third test | = | 12 |
| 5 | my group is the best | this attribute rocks | = | 12 |
| 6 | This other group rocks | Second type of insert method | + | 11 |
+----+------------------------+------------------------------+----+-------+
6 rows in set (0.01 sec)

and confirm the new row is now in the table.

The UPDATE Command to Update Rows / Fields in a Table

Using UPDATE you can change the contents of existing rows in a table. This command is quite versatile and will become handy in any SQL project you work on. We'll show you a couple of basic examples of how to use the UPDATE command to get you started.

Update a single value from a row

The smallest change you can make to a table is to update a single value from a specific row. To change just one value you need a unique identifier for the row you wish to change. Every row in a given table has a unique row id that is recorded in the id column. To make sure you update the right row use the WHERE statement combined with the desired value from the id column as in the example below.

mysql> UPDATE radgroupreply SET value='9' WHERE groupname='test1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Our example should have changed the value for the groupname “test 1” to “9”. We can confirm that change using the following SELECT command:

mysql> SELECT value FROM radgroupreply WHERE groupname="test1";
+-------+
| value |
+-------+
| 9 |
+-------+
1 row in set (0.01 sec)

Update multiple values in multiple rows

You can also update multiple rows at the same time. We have provided a simple example of such an UPDATE command in which we change the value fields to “0” for all the rows in which the value of the op field is “=".

mysql> UPDATE radgroupreply SET value="0" WHERE op="=";
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Again, we will use the SELECT command to check whether the desired changes occurred. Given that several rows were affected, it will be easier to figure out if the changes have been made if we print the whole table like so.

mysql> SELECT * FROM radgroupreply;
+----+------------------------+------------------------------+----+-------+
| id | groupname | attribute | op | value |
+----+------------------------+------------------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 0 |
| 3 | test2 | This is a second test | = | 0 |
| 4 | test3 | This is a third test | = | 0 |
| 5 | my group is the best | this attribute rocks | = | 0 |
| 6 | This other group rocks | Second type of insert method | + | 11 |
+----+------------------------+------------------------------+----+-------+
6 rows in set (0.00 sec)

The results of this latest SELECT should show that the value fields have been updated to "0" for all rows where the op field was "=".

DELETE rows in a table

There are times when you need to delete data from your tables. Such deletions can be done one at a time or in bulk.

Deleting a single row

The safest way to delete a specific single row is to specify that row in your DELETE command based on the id column. The following command is an example of this approach:

mysql> DELETE FROM radgroupreply WHERE id="6";
Query OK, 1 row affected (0.00 sec)

If you print the full table, using the command below, you should see that the row with id value “6” is no longer present in the table.

mysql> SELECT * FROM radgroupreply;
+----+----------------------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+----------------------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 0 |
| 3 | test2 | This is a second test | = | 0 |
| 4 | test3 | This is a third test | = | 0 |
| 5 | my group is the best | this attribute rocks | = | 0 |
+----+----------------------+-----------------------+----+-------+
5 rows in set (0.00 sec)

Deleting a multiple rows

You will often need to remove multiple fields from a table for housekeeping reasons. MySQL tables can become quite large and it's often wise to clean them up for manageability and performance purposes.

Below we'll show you how to delete multiple rows based on a common factor. For this example, we'll delete ALL the rows for which the amount in the value column is below 15. Given the data in our table, that should mean that all rows other than the first row will be deleted when you enter this command:

mysql> DELETE FROM radgroupreply WHERE value<"15";
Query OK, 4 rows affected (0.00 sec)

With the following SELECT command, we can confirm that all rows have been deleted other than the one where the value column indicates a value of 15 or above.

mysql> SELECT * FROM radgroupreply;
+----+-----------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
+----+-----------+-----------------------+----+-------+
1 row in set (0.00 sec)

Conclusion

Now you know all the basic commands needed to administer MySQL data. There are myriad variations on the above commands that will afford you greater and greater control over the behavior and content of MySQL databases. The best way to learn these variations and the efficiency that comes of using them is to take what you have learned in this tutorial and play with it.

How to Install Cassandra on CentOS 7

Cassandra is a reliable, clusterable, highly-scalable database capable of handling large quantities of data on commodity hardware. If you have big data needs, and are looking for a proven open source solution that has received battle testing from many large companies, then Cassandra may be exactly what you’re looking for. If you have a CentOS 7 server, this guide will get you up and running with a single Cassandra node. It will use pre-packaged Cassandra distributions built for CentOS, making installation and upgrades a snap. You can then build it out by performing additional installations on other servers, then clustering the resulting instances for higher scalability and reliability. This article will guide you on how to install Apache Cassandra on CentOS 7 Server.

Getting Started

You’ll need the following in place in order to complete this guide:
• 1 server (Cloud Server or Dedicated Server) with CentOS 7
• Root access

Tutorial

Begin by applying all available patches and security updates to your CentOS 7 server.

yum -y update

Cassandra is written in Java. As such, you’ll need the Java VM installed in order to run it. Do so via Yum.

yum -y install java

Packages for Cassandra are shipped in a Datastax repository. In order to automatically retrieve these packages, and to fetch upgrades in the future, we’ll create this repository so Yum can use it.

nano /etc/yum.repos.d/datastax.repo

[datastax]
name = DataStax Repo for Apache Cassandra
baseurl = http://rpm.datastax.com/community
enabled = 1
gpgcheck = 0

Now Cassandra is ready to be installed.

yum -y install dsc20

With Cassandra installed, we must now start the daemon via systemd.
/etc/init.d/cassandra start

The systemd unit is now created. Use “systemctl start cassandra” to launch the new unit.

systemctl enable cassandra.service

While the database should be running, it is not yet configured to launch on boot. Let’s tell systemd that Cassandra should automatically launch whenever your system boots.

[root@cassandra ~] systemctl status cassandra

cassandra.service - SYSV: Starts and stops Cassandra
Loaded: loaded (/etc/rc.d/init.d/cassandra)
Active: active (exited) since Thu 2016-09-15 04:36:47 UTC; 14s ago
Docs: man:systemd-sysv-generator(8)
Process: 9413 ExecStart=/etc/rc.d/init.d/cassandra start (code=exited, status=0/SUCCESS)

Let’s ensure that Cassandra is running using this command.

[root@cassandra ~] cqlsh

Connected to Test Cluster at localhost:9160.
[cqlsh 4.1.1 | Cassandra 2.0.17 | CQL spec 3.1.1 | Thrift protocol 19.39.0]
Use HELP for help.
cqlsh>

Cassandra ships with a powerful command line utility, cqlsh. Launch it to perform various vital tasks with your database.

[root@cassandra ~] nodetool status

Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
-- Address Load Tokens Owns (effective) Host ID Rack
UN 127.0.0.1 46.21 KB 256 100.0% 7dd2b7d9-404e-4a77-a36d-cc8f55168c0d rack1
[root@cassandra ~]#

Likewise, the nodetool command shows the status of your Cassandra node.

Conclusion

You now have a fresh Cassandra instance into which you can load large amounts of data. Share this guide with anyone you may know of who is in search of a big data solution, so they too can enjoy the benefits of an easy Cassandra installation. If you found this article helpful, feel free to share it with your friends and let us know in the comments below!

How to Install Cassandra on Ubuntu 16

Cassandra is an open-source non-relational database management system. It was designed to be used with lots of redundancy, and it can be split up onto multiple machines so that if one part fails, the other parts continue to with little to no interruption to users. Ubuntu is a great operating system to host on as it won’t bog down the system. Even if the command line scares you at first, It’s not that hard to install. This article will guide you on how to install Apache Cassandra on Ubuntu 16.04 Server LTS.

Getting Started

To complete this guide, you will need the following:
• 1 server (Cloud Server or Dedicated Server) running Ubuntu 16.
• Root access

Gaining root permission is simple and prevents bugs from popping up during installation. Just make sure to exit out of the root when you’re done with setup.

Tutorial

Install Java
First we need to make sure that Java is installed, so add the repository. Repositories are basically groups or lists of programs that are useful together, and adding a repository is the best way to get a program. You may get a warning to use Java 8 instead of 9; don’t worry, continue on.

add-apt-repository ppa:webupd8team/java

After that’s added, update the list of repositories.

apt-get update

Install Java. Answer yes if you get a confirmation for storage and accept the license terms. Installing might take a few moments depending on your computer’s speed.

apt-get install oracle-java8-set-default

Confirm your version of Java. At the time of writing, the latest stable version is 1.8.

java -version

java version "1.8.0_101"
Java(TM) SE Runtime Environment (build 1.8.0_101-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.101-b13, mixed mode)

Install Cassandra

Just like with Java, we need to add the repository. Since these are protected files, you need to provide your email and password associated with Datastax. Depending on your exact computer setup, you may need to add a backslash (\) to parts of your email with special characters. Instead of
“Ma!lLover@Globo.Tech,” it would be “Ma\!lLover@Globo.Tech”. You might also have to replace “@” with “%40”.

echo "deb http://debian.datastax.com/community stable main" | sudo tee -a /etc/apt/sources.list.d/cassandra.sources.list

Also add the repository key to get past the extra layer of security.

curl -L http://debian.datastax.com/debian/repo_key | sudo apt-key add -

Update your list a third time and install Casandra the same way as the last two.

apt-get update
apt-get install cassandra -y

Start Cassandra up and configure it to your liking. You’ll most likely want to enable it to start on boot. In case of a power outage or maintenance, you won’t forget to start it back up after a reboot.

systemctl start cassandra
systemctl enable cassandra

Cassandra uses a separate command line to be controlled, so we need to make sure to activate that.

[root@cassandra ~]# cqlsh
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.0.8 | CQL spec 3.4.0 | Native protocol v4]
Use HELP for help.
cqlsh>

You may want to check information about the node and cluster to get an idea of how to fix various issues or update information. You can do that using the “nodetool” command:

[root@cassandra ~] nodetool status
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
-- Address Load Tokens Owns (effective) Host ID Rack
UN 127.0.0.1 222.14 KB 256 100.0% 2a0b7fa9-23c6-40d3-83a4-e6c06e2f5736 rack1
[root@cassandra ~]#

Conclusion

You should now have Java and Cassandra installed and ready to use. Make sure to share this guide with others looking for help.

How to Install Cassandra on Ubuntu 14

Apache Cassandra is a distributed database system designed to manage massive amounts of data, fast, without losing sleep. If you have big data needs that require clusters of servers, and don’t want to struggle with traditional relational databases and their associated scaling difficulties, then Cassandra may be your solution. Proven in dozens of high-profile companies, it provides highly available replicated data storage on commodity hardware or cloud infrastructure. It also integrates with existing monitoring and logging frameworks, both essential components of a resilient cloud deployment.

Getting Started

This guide will get you up and running with a Cassandra database instance which you can later scale up. It expects that you have the following:
• 1 server (Cloud Server or Dedicated Server) running Ubuntu 14.
• Root access

Tutorial

Install Java
Cassandra requires Java 1.8, which is unfortunately not shipped with this distribution. Begin by adding this package repository which includes the necessary Java version.

add-apt-repository ppa:webupd8team/java
apt-get update

With the repository installed, Java 1.8 should be available. Install that next.

apt-get install oracle-java8-set-default

The installation should have succeeded. To confirm that it worked, let’s check the Java version by running the below command.

java -version

java version "1.8.0_101"
Java(TM) SE Runtime Environment (build 1.8.0_101-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.101-b13, mixed mode)

With the correct Java version installed, we’ll next install the Datastax repository. This makes Cassandra available via your package manager, thus easing the initial installation and future upgrades.

echo "deb http://debian.datastax.com/community stable main" | sudo tee -a /etc/apt/sources.list.d/cassandra.sources.list

You’ll also need the trusted key for this repository. The key lets your package manager confirm that the packages it downloads are those actually built by the Datastax maintainers.

curl -L http://debian.datastax.com/debian/repo_key | sudo apt-key add -

Install Cassandra

With these pieces in place, we can now update your package list and install the Cassandra package.

apt-get update
apt-get install cassandra -y

If you want to enter the Cassandra command line, use the cqlsh command as shown:

[root@cassandra ~]# cqlsh
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.0.8 | CQL spec 3.4.0 | Native protocol v4]
Use HELP for help.
cqlsh>

You can also check the status of your Cassandra nodes with the “nodetool” command:

[root@cassandra ~]# nodetool status
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
-- Address Load Tokens Owns (effective) Host ID Rack
UN 127.0.0.1 102.78 KB 256 100.0% 05d9accc-9404-438c-b03b-869432439042 rack1
[root@cassandra ~]#

Conclusion

Cassandra is up and running, ready to be used directly or scaled out across a cluster. If you know anyone looking for a quality solution for their big data needs, share this article so they too can enjoy the benefits of this robust database solution.

How to setup a MySQL Master-Slave Replication on CentOS 7

MySQL replication is a technique by which a master database will be automatically copied to one or more slave databases, making data backup, recovery, and analysis a much easier prospect. The following guide will walk you through a basic MySQL replication setup using just two servers, one master and one slave. We’ll also be using MariaDB, a popular drop-in replacement for MySQL.

Getting Started

Before you begin, make sure you have the following:
• 2 servers (Cloud Server or Dedicated Server), both running a fresh installation of CentOS 7
• Root access to both machines

Tutorial

Note: these are the local IP addresses our servers will be using for the purposes of this guide.
• Server Master IP: 10.0.0.179
• Server Slave IP: 10.0.0.180

Before starting, we need to make sure that Selinux is disabled on each of our servers.

setenforce 0
sed -i 's/enforcing/disabled/' /etc/sysconfig/selinux
sed -i 's/enforcing/disabled/' /etc/selinux/config

Let’s also install MariaDB on each server, making sure to secure the installation.

yum install mariadb-server mariadb -y
systemctl start mariadb.service
mysql_secure_installation

Now we can begin the setup, focusing first on the master server. Go to my.cnf on the master server and add these 3 lines:

nano /etc/my.cnf

[mysqld]
...
server_id=1
log-bin
replicate-do-db=globotech

Replace globotech with the name of the database that you want to replicate.

To save the changes, you will have to restart MariaDB.

systemctl restart mariadb.service

Now let’s load up MariaDB itself so that we can better configure the replication.

mysql -u root -p

MariaDB [(none)]> grant replication slave on *.* to 'slave_user'@'%' identified by 'globopassword';
MariaDB [(none)]> flush privileges;

Replace slave_user with the slave username and globopassword with its password.
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 475 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Make a note of the log file “mariadb-bin.000001” and the log position “475” for later on in the guide.
MariaDB [(none)]> exit

The next step is to configure with the slave server. Like before, open up its my.cnf and add these 2 lines:
[mysqld]
...
server_id=2
replicate-do-db=globotech

Of course, replace globotech with the name of your own database.

Next, restart MariaDB so the changes will be recognized.

systemctl restart mariadb.service

We will now configure the slave to connect to the master.

mysql -u root -p

MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='10.0.0.179' , master_user='slave_user' , master_password='globopassword' , master_log_file='mariadb-bin.000001' , master_log_pos=475;
MariaDB [(none)]> start slave;

Replace 10.0.0.179 with the IP address for your master server, and add in the log file and log position that you noted earlier.

We’re almost done. Let’s check the replication status.

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.179
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 475
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: globotech
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 475
Relay_Log_Space: 827
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Finally, let’s test that replication is actually occurring. Log in to the master server and enter MariaDB.

mysql -u root -p

Create the database globotech and insert a table.
MariaDB [(none)]> create database globotech;
MariaDB [(none)]> use globotech;
MariaDB [globotech]> create table staff (c int);
MariaDB [globotech]> insert into staff (c) values (3);
MariaDB [globotech]> select * from staff;
+------+
| c |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Once logging into the slave server, you should see the same database and table.
mysql -u root -p

MariaDB [(none)]> use globotech;
MariaDB [globotech]> select * from staff;
+------+
| c |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Conclusion

With MySQL (or in this case, MariaDB) replication, you’ll be able to better protect your databases in the case of failure, and safely analyse data without worrying about corrupting your production copy. If you found this article helpful, feel free to share it with your friends and let us know in the comments below!