MYSQL FOR DATABASE AND BACKUP

What is Mysql

First things first, you have to know how to pronounce it: MY-ES-KYOO-EL’ [maɪˌɛsˌkjuːˈɛl]. Sometimes people call it “my sequel” or other names, but at least you know the official pronunciation. A Swedish company called MySQL AB originally developed MySQL in 1994. The US tech company Sun Microsystems then took full ownership when they bought MySQL AB in 2008. US tech giant Oracle in 2010 acquired Sun Microsystems itself, and MySQL has been practically owned by Oracle since.

In regard to the general definition, MySQL is an open source relational database management system (RDBMS) with a client-server model. RDBMS is a software or service used to create and manage databases based on a relational model. Now, let’s take a closer look at each term:

Database

A database is simply a collection of structured data. Think of taking a selfie: you push a button and capture an image of yourself. Your photo is data, and your phone’s gallery is the database. A database is a place in which data is stored and organized. The word “relational” means that the data stored in the dataset is organized as tables. Every table relates in some ways. If the software doesn’t support the relational data model, just call it DBMS.

Client-server model

Computers that install and run RDBMS software are called clients. Whenever they need to access data, they connect to the RDBMS server. That’s the “client-server” part.

MySQL is one of many RDBMS software options. RDBMS and MySQL are often thought to be the same because of MySQL’s popularity. A few big web applications like Facebook, Twitter, YouTube, Google, and Yahoo! all use MySQL for data storage purposes. Even though it was initially created for limited usage, it is now compatible with many important computing platforms like Linux, macOS, Microsoft Windows, and Ubuntu.

Let us learn how to install Mysql version 5.7

Requirements

For you to install Mysql 5.7 on both servers you need to Copy the key here to your clipboard

https://dev.mysql.com/doc/refman/5.7/en/checking-gpg-signature.html

Save it in a file on your server web-01 and web-02 i.e. signature.key and then

$ sudo apt-key add signature.key

add the apt repo

$ sudo sh -c 'echo "deb http://repo.mysql.com/apt/ubuntu bionic mysql-5.7" >> /etc/apt/sources.list.d/mysql.list'

update apt

$ sudo apt-get update

now check your available versions:

$ sudo apt-cache policy mysql-server
mysql-server:
  Installed: (none)
  Candidate: 8.0.27-0ubuntu0.20.04.1
  Version table:
     8.0.27-0ubuntu0.20.04.1 500
        500 http://archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu focal-security/main amd64 Packages
     8.0.19-0ubuntu5 500
        500 http://archive.ubuntu.com/ubuntu focal/main amd64 Packages
     5.7.37-1ubuntu18.04 500
        500 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 amd64 Package

Now install mysql 5.7

$ sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7*

How to create Mysql Username and password

We shall use the example below:

In order for us to verify that your servers are properly configuredCreate a MySQL user named holberton_user on both web-01 and web-02 with the host name set to localhost and the password projectcorrection280hbtn. This will allow us to access the replication status on both servers.

Creating a New User

After the installation of Mysql on your ubuntu servers web-01, web-02.

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This plugin requires that the name of the operating system user that invokes the MySQL client matches the name of the MySQL user specified in the command. This means that you need to precede the mysql command with sudo to invoke it with the privileges of the root Ubuntu user in order to gain access to the root MySQL user:

ubuntu@229-web-01:~$ sudo mysql

Run the following command to create a user that authenticates with caching_sha2_password. Be sure to change holberton_user to your preferred username and password to a strong projectcorrection280hbtn of your choosing:

mysql> CREATE USER 'holberton_user'@'localhost' IDENTIFIED BY 'projectcorrection280hbtn';

Make sure that holberton_user has permission to check the primary/replica status of your databases.

GRANT REPLICATION CLIENT ON *.* to 'holberton_user'@'localhost';

Next, flush the privileges with the following command:

mysql> FLUSH PRIVILEGES;

For verification:

ubuntu@229-web-01:~$ mysql -uholberton_user -p -e "SHOW GRANTS FOR 'holberton_user'@'localhost'"
Enter password:
+-----------------------------------------------------------------+
| Grants for holberton_user@localhost                             |
+-----------------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO 'holberton_user'@'localhost' |
+-----------------------------------------------------------------+
ubuntu@229-web-01:~$

We do the following:

• Create a database named tyrell_corp.
• Within the tyrell_corp database create a table named nexus6 and add at least one entry to it.
• Make sure that holberton_user has SELECT permissions on your table so that we can check that the table exists and is not empty.

mysql> CREATE DATABASE IF NOT EXISTS tyrell_corp; mysql> GRANT SELECT ON TABLE tyrell_corp.* TO 'holberton_user'@'localhost'; # Once login, stop the slave server `(web-02)` to connect to the master server: mysql> STOP SLAVE; # Run the following command to allow the slave server `(web-02)` to replicate the Master server: mysql> CHANGE MASTER TO MASTER_HOST ='your-master-ip', MASTER_USER ='replica_user', MASTER_PASSWORD ='password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 900; # you can use any details for your table. INSERT INTO nexus6 (name) VALUES ('MG'), ('Mustapha'), ('Galadima'); # Next, start the SLAVE with the following command: mysql> START SLAVE;

• In order for you to set up replication, you’ll need to have a database with at least one table and one row in your primary MySQL server (web-01) to replicate from.

We do the following:

• Create a database named tyrell_corp.
• Within the tyrell_corp database create a table named nexus6 and add at least one entry to it.
• Make sure that holberton_user has SELECT permissions on your table so that we can check that the table exists and is not empty.

$ sudo mysql

mysql> CREATE DATABASE IF NOT EXISTS tyrell_corp;

mysql> GRANT SELECT ON TABLE tyrell_corp.* TO 'holberton_user'@'localhost';

# Once login, stop the slave server `(web-02)` to connect to the master server:

mysql> STOP SLAVE;

# Run the following command to allow the slave server `(web-02)` to replicate the Master server:

mysql> CHANGE MASTER TO MASTER_HOST ='your-master-ip', MASTER_USER ='replica_user', MASTER_PASSWORD ='password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 900;

# you can use any details for your table.

INSERT INTO nexus6 (name) VALUES ('MG'), ('Mustapha'), ('Galadima'); 

# Next, start the SLAVE with the following command:

mysql> START SLAVE;

Display the table created:

ubuntu@229-web-01:~$ mysql -uholberton_user -p -e "use tyrell_corp; select * from nexus6"
Enter password:
+----+-------+
| id | MG  |
+----+-------+
|  1 | Mustapha  |
+----+-------+
| 2  | Galadima  |
+----+-------+
ubuntu@229-web-01:~$

Before you get started with your primary-replica synchronization, you need one more thing in place. On your primary MySQL server (web-01), create a new user for the replica server.

• The name of the new user should be replica_user, with the host name set to %, and can have whatever password you'd like.
• replica_user must have the appropriate permissions to replicate your primary MySQL server.
• holberton_user will need SELECT privileges on the mysql.user table in order to check that replica_user was created with the correct permissions.

$ sudo mysql
mysql> CREATE USER IF NOT EXISTS 'replica_user'@'%' IDENTIFIED BY 'replica_user';
mysql> GRANT REPLICATION SLAVE ON *.* to 'replica_user'@'%';
mysql> GRANT SELECT ON TABLE mysql.user TO 'holberton_user'@'localhost';

Try checking it:

ubuntu@229-web-01:~$ mysql -uholberton_user -p -e 'SELECT user, Repl_slave_priv FROM mysql.user'
+------------------+-----------------+
| user             | Repl_slave_priv |
+------------------+-----------------+
| root             | Y               |
| mysql.session    | N               |
| mysql.sys        | N               |
| debian-sys-maint | Y               |
| holberton_user   | N               |
| replica_user     | Y               |
+------------------+-----------------+
ubuntu@229-web-01:~$

Setup a Primary-Replica infrastructure using MySQL

server 1 web-01

Make sure that UFW is allowing connections on port 3306 (default MySQL port) otherwise replication will not work.

$ sudo ufw allow from `web-02-ip-address` to any port 3306

$ sudo service mysql restart

#open new terminal

#ssh into server1 `web-01`

$ sudo mysqldump -u root tyrell_corp > tyrell_corp.sql

Now we have to, edit the MySQL default configuration file in web-01:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Edit the file as follows:

for the primary web-01:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address          = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
#: the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
binlog_do_db            = tyrell_corp
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

server 2 web-02

CHANGE MASTER TO
MASTER_HOST='web-02-ip-address',
MASTER_USER='replica_user',
MASTER_PASSWORD='replica_user',
MASTER_LOG_FILE='mysql-bin.000001', # mysql-bin for the web-01, you get it when run this command `mysql> show master status;`
MASTER_LOG_POS=306;

for the replica web-02:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address          = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
binlog_do_db            = tyrell_corp
relay-log               = /var/log/mysql/mysql-relay-bin.log
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

How to create MySQL backup

1. Connect to MySQL server via command line

$ mysql -username -p

2. Enter the password and hit enter

3. Take a backup of the database named ABC-database with this command

$ mysqldump mysql-database > ABC-database-backup.sql

These commands will backup database ABC-database into a SQL dump with the name ABC-database-backup.sql.

You need to know the name of the database you want to make a backup of.

There is a simple command that can display all the database name:

$ show databases

You can also multiple backup databases with mysqldump command.

Enter this command:

$ mysqldump --databases database_ABC database_XYZ > ABCXYZ_databases.sql

This is the command for backing up all the MySQL Database at once:

$ mysqldump --all-databases > all_databases.sql

This is only one way to backup and restores the MySQL Database, among many.

GitHub >> Mustapha Aliyu Galadima