Data serves as the foundation of business and operations, and with various possible threats (e.g., attackers, system failures, bad upgrades, and maintenance errors) out there, backups remain critical. These backups take many forms, and many options exist for creating them with an even wider set of options within those processes. The important things to remember are the database type, the critical information, and the structure involved. This information determines your best option.

OPTIONS

The main options for backups include logical backups and physical backups. Logical backups hold SQL statements for restoring data. Physical backups contain copies of data.

• Logical backups offer the flexibility of restoring data on another machine with a different configuration in contrast to physical backups, which are often limited to the same machine and database type. Logical backups occur at database and table level, and physical occur at directory and file level.

• Physical backups are smaller in size than logical, and also take less time to perform and restore. Physical backups also include log and configuration files, but logical backups do not.

Backup Tools

The main tool used for MariaDB backups is mysqldump. It offers logical backups and flexibility. It also proves an excellent option for small databases. Mysqldump dumps data into SQL, CSV, XML, and many other formats. Its output does not retain stored procedures, views, and events without explicit instruction.

There are three options for mysqldump backups −

• Raw data − Dump a table as a raw data file through the –tab option, which also specifies the destination of the file −

$ mysqldump -u root -p --no-create-info
   --tab=/tmp PRODUCTS products_tbl

• Data/Definitions export − This option allows a single or multiple tables to be exported to a file, and supports backing up all existing databases on the host machine. Examine an example of exporting contents or definitions to a file

$ mysqldump -u root -p PRODUCTS products_tbl > export_file.txt

• Transfer − You can also output databases and tables to another host

$ mysqldump -u root -p database_name
   | mysql -h other-host.com database_name

Using THE SELECT…INTO OUTFILE Statement

Another option for exporting data employs the SELECT…INTO OUTFILE statement. This simple option outputs the table into a simple formatted text file −

mysql> SELECT * FROM products_tbl
   -> INTO OUTFILE ''/tmp/products.txt

Its attributes allow formatting the file to your preferred specifications.

Note the following qualities of this statement −

• The file name must specify your desired location for the output.

• You need MariaDB file privileges to execute the statement.

• The output file name must be unique.

• You need login credentials on the host.

• In a UNIX environment, the output file is world readable, but its server ownership affects your ability to delete it. Ensure you have privileges.

Using CONNECT in Backups

The CONNECT handler allows exporting of data. This proves useful primarily in situations when the SELECT…INTO OUTFILE operation does not support the file format.

Review the following example −

create table products
engine = CONNECT table_type = XML file_name = ''products.htm'' header = yes
option_list = ''name = TABLE,coltype = HTML,attribute = border = 1;cellpadding = 5''

select plugin_name handler, plugin_version version, plugin_author
author, plugin_description description, plugin_maturity maturity
from information_schema.plugins where plugin_type = ''STORAGE ENGINE

Other Tools

Other options for backups are as follows −

• XtraBackup − This option targets XtraDB/InnoDB databases and works with any storage engine. Learn more about this tool from Percona”s official site.

• Snapshots − Some filesystems allow snapshots. The process consists of flushing the tables with read lock, mounting the snapshot, unlocking the tables, copying the snapshot, and then unmounting the snapshot.

• LVM − This popular method employs a Perl script. It gets a read lock on every table and flushes caches to disk. Then it gets a snapshot and unlocks the tables. Consult the official mylvmbackup website for more information.

• TokuBackup − This solution provided by Percona provides hot backups taking into account the problems and limitations of InnoDB backup options. It produces a transactional sound copy of files while applications continue to manipulate them.Consult the Percona website for more information..

INNODB Considerations

InnoDB uses a buffer pool for performance enhancement. In a backup, configure InnoDB to avoid copying an entire table into the buffer pool because logical backups typically perform full table scans.

In version 10.0.3, MariaDB introduced a storage engine known as sequence. Its ad hoc generates an integer sequence for operations, and then it terminates. The sequence contains positive integers in descending or ascending order, and uses a starting, ending, and increment value.

It does not allow use in multiple queries, only in its original query because of its virtual (not written to disk) nature. However, sequence tables can be converted to standard tables through an ALTER command. If a converted table is deleted, the sequence table still exists. Sequences also cannot produce negative numbers or rotate at the minimum/maximum.

Installing the Sequence Engine

Using sequences requires installing the sequence engine, which MariaDB distributes as a plugin rather than binary. Install it with the following command −

INSTALL SONAME "ha_sequence";

After installation, verify it −

SHOW ENGINESG

Remember that after engine installation, you cannot create a standard table with a name that uses sequence syntax, but you can create a temporary table with a sequence-syntax name.

Creating Sequence

There are two methods of sequence creation −

• Create a table and use the AUTO_INCREMENT attribute to define a column as auto-increment.

• Use an existing database and use a sequence SELECT query to produce a sequence. The query uses seq_ [FROM] _to_[TO] or seq_[FROM]_to_[TO]_step_STEP syntax.

Best practices prefer the use of the second method. Review an example of a sequence creation given below −

SELECT * FROM seq_77_to_99;

Sequences have many uses −

• Locate missing values within a column to protect against related issues in operations −

SELECT myseq.seq FROM seq_22_to_28 myseq LEFT JOIN table1 t ON myseq.seq
   = x.y WHERE x.y IS NULL;

• Construct a combination of values −

SELECT x1.seq, x2.seq FROM seq_5_to_9 x1 JOIN seq_5_to_9 x2 ORDER BY 5, 6;

• Find multiples of a number −

SELECT seq FROM seq_3_to_100_step_4;

• Construct a date sequence for use in applications like booking systems.
• Construct a time sequence.

Some situations require producing an exact copy of an existing table. The CREATE…SELECT statement cannot produce this output because it neglects things like indexes and default values.

The procedure for a duplicating a table is as follows −

• Utilize SHOW CREATE TABLE to produce a CREATE TABLE statement that details the entire structure of the source table.

• Edit the statement to give the table a new name, and execute it.

• Use an INSERT INTO…SELECT statement if you also need the table data copied.

mysql> INSERT INTO inventory_copy_tbl (
   product_id,product_name,product_manufacturer,ship_date)

   SELECT product_id,product_name,product_manufacturer,ship_date,
   FROM inventory_tbl;

Another method for creating a duplicate uses a CREATE TABLE AS statement. The statement copies all columns, column definitions, and populates the copy with the source table”s data.

Review its syntax given below −

CREATE TABLE clone_tbl AS
   SELECT columns
   FROM original_tbl
   WHERE conditions];

Review an example of its use below −

CREATE TABLE products_copy_tbl AS
   SELECT *
   FROM products_tbl;

The simple act of accepting user input opens the door to exploits. The problem stems primarily from the logical management of data, but luckily, it is fairly easy to avoid these major flaws.

Opportunities for SQL injection typically occur on users entering data like a name, and the code logic failing to analyze this input. The Code, instead, allows an attacker to insert a MariaDB statement, which will run on the database.

Always consider data entered by users, suspect and are in need of strong validation prior to any processing. Perform this validation through pattern matching. For example, if the expected input is a username, restrict entered characters to alphanumeric chars and underscores, and to a certain length. Review an example given below −

if(check_match("/^w{8,20}$/", $_GET[''user_name''], $matches)) {
   $result = mysql_query("SELECT * FROM system_users WHERE user_name = $matches[0]");
} else {
   echo "Invalid username";
}

Also, utilize the REGEXP operator and LIKE clauses in creating input constraints.

Consider all types of necessary explicit control of input such as −

• Control the escape characters used.

• Control the specific appropriate data types for input. Limit input to the necessary data type and size.

• Control the syntax of entered data. Do not allow anything outside of the needed pattern.

• Control the terms permitted. Blacklist SQL keywords.

You may not know the dangers of injection attacks, or may consider them insignificant, but they top the list of security concerns. Furthermore, consider the effect of these two entries −

1=1
-or-
*

Code allowing either of those to be entered along with the right command may result in revealing all user data on the database or deleting all data on the database, and neither injection is particularly clever. In some cases, attackers do not even spend time examining holes; they perform blind attacks with simple input.

Also, consider the pattern matching and regular expression tools provided by any programming/scripting language paired with MariaDB, which provide more control, and sometimes better control.

Some operations can benefit from temporary tables due to speed or disposable data. The life of a temporary table ends at the termination of a session whether you employ them from the command prompt, with a PHP script, or through a client program. It also does not appear in the system in a typical fashion. The SHOW TABLES command will not reveal a list containing temporary tables.

Create a Temporary Table

The TEMPORARY keyword within a CREATE TABLE statement spawns a temporary table. Review an example given below −

mysql>CREATE TEMPORARY TABLE order (
   item_name VARCHAR(50) NOT NULL
   , price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   , quantity INT UNSIGNED NOT NULL DEFAULT 0
);

In creating a temporary table, you can clone existing tables, meaning all their general characteristics, with the LIKE clause. The CREATE TABLE statement used to spawn the temporary table will not commit transactions as a result of the TEMPORARY keyword.

Though temporary tables stand apart from non-temporary and drop at the end of a session, they may have certain conflicts −

• They sometimes conflict with ghost temporary tables from expired sessions.

• They sometimes conflict with shadow names of non-temporary tables.

Note − Temporary tables are permitted to have the same name as an existing non-temporary table because MariaDB views it as a difference reference.

Administration

MariaDB requires granting privileges to users for creating temporary tables. Utilize a GRANT statement to give this privilege to non-admin users.

GRANT CREATE TEMPORARY TABLES ON orders TO ''machine122''@''localhost

Drop a Temporary Table

Though temporary tables are essentially removed at the end of sessions, you have the option to delete them. Dropping a temporary table requires the use of the TEMPORARY keyword, and best practices suggest dropping temporary tables before any non-temporary.

mysql> DROP TABLE order;

MariaDB, as discussed in earlier lessons, allows duplicate records and tables in some situations. Some of these duplicates are not in fact duplicates due to distinct data or object types, or as a result of unique lifespan or storage of the operation object. These duplicates also typically pose no problems.

In some situations, duplicates do cause problems, and they often appear due to implicit actions or the lenient policy of a MariaDB command. There are ways to control this issue, find duplicates, delete duplicates, and prevent duplicate creation.

Strategies and Tools

There are four key ways to manage duplicates −

• Fish for them with JOIN, and delete them with a temporary table.

• Use INSERT…ON DUPLICATE KEY UPDATE to update on discovery of a duplicate.

• Use DISTINCT to prune the results of a SELECT statement and remove duplicates.

• Use INSERT IGNORE to stop insertion of duplicates.

Using Join with a Temporary Table

Simply perform a semi-join like an inner join, and then remove the duplicates found with a temporary table.

Using INSERT

When INSERT…ON DUPLICATE KEY UPDATE discovers a duplicate unique or primary key, it performs an update. On discovery of multiple unique keys, it updates only the first. Hence, do not use it on tables with multiple unique indices.

Review the following example, which reveals what happens in a table containing indexed values on insertion into a populated field −

INSERT INTO add_dupl VALUES (1,''Apple'');
ERROR 1062 (23000): Duplicate entry ''1'' for key ''PRIMARY''

Note − If it finds no key, an INSERT…ON DUPLICATE KEY UPDATE statement executes like a normal insert statement.

Using DISTINCT

DISTINCT clauses remove duplicates from results. The general syntax for a DISTINCT clause is as follows −

SELECT DISTINCT fields
FROM table
[WHERE conditions];

Note − The results of a statement with a DISTINCT clause −

• When using one expression, it returns unique values for it.

• When using multiple expressions, it returns unique combinations.

• It does not ignore NULL values; thus, results also contain NULLs as unique values.

Review the following statement using a DISTINCT clause for a single expression −

SELECT DISTINCT product_id
FROM products
WHERE product_name = ''DustBlaster 5000

Review the following example using multiple expressions −

SELECT DISTINCT product_name, product_id
FROM products
WHERE product_id < 30

Using INSERT IGNORE

An INSERT IGNORE statement instructs MariaDB to cancel insertion on discovery of a duplicate record. Review an example of its use given below −

mysql> INSERT IGNORE INTO customer_tbl (LN, FN)
   VALUES( ''Lex'', ''Luther'');

Also, note the logic behind duplicates. Some tables require duplicates based on the nature of that table data. Accommodate that need in your strategy for managing duplicate records.

The ALTER command provides a way to change an existing table”s structure, meaning modifications like removing or adding columns, modifying indices, changing data types, or changing names. ALTER also waits to apply changes when a metadata lock is active.

Using ALTER to Modify Columns

ALTER paired with DROP removes an existing column. However, it fails if the column is the only remaining column.

Review the example given below −

mysql> ALTER TABLE products_tbl DROP version_num;

Use an ALTER…ADD statement to add columns −

mysql> ALTER TABLE products_tbl ADD discontinued CHAR(1);

Use the keywords FIRST and AFTER to specify placement of the column −

ALTER TABLE products_tbl ADD discontinued CHAR(1) FIRST;
ALTER TABLE products_tbl ADD discontinued CHAR(1) AFTER quantity;

Note the FIRST and AFTER keywords only apply to ALTER…ADD statements. Furthermore, you must drop a table and then add it in order to reposition it.

Change a column definition or name by using the MODIFY or CHANGE clause in an ALTER statement. The clauses have similar effects, but utilize substantially different syntax.

Review a CHANGE example given below −

mysql> ALTER TABLE products_tbl CHANGE discontinued status CHAR(4);

In a statement using CHANGE, specify the original column and then the new column that will replace it. Review a MODIFY example below −

mysql> ALTER TABLE products_tbl MODIFY discontinued CHAR(4);

The ALTER command also allows for changing default values. Review an example −

mysql> ALTER TABLE products_tbl ALTER discontinued SET DEFAULT N;

You can also use it to remove default constraints by pairing it with a DROP clause −

mysql> ALTER TABLE products_tbl ALTER discontinued DROP DEFAULT;

Using ALTER to Modify Tables

Change table type with the TYPE clause −

mysql> ALTER TABLE products_tbl TYPE = INNODB;

Rename a table with the RENAME keyword −

mysql> ALTER TABLE products_tbl RENAME TO products2016_tbl;

Indexes are tools for accelerating record retrieval. An index spawns an entry for each value within an indexed column.

There are four types of indexes −

• Primary (one record represents all records)

• Unique (one record represents multiple records)

• Plain

• Full-Text (permits many options in text searches).

The terms “key” and “index” are identical in this usage.

Indexes associate with one or more columns, and support rapid searches and efficient record organization. When creating an index, consider which columns are frequently used in your queries. Then create one or multiple indexes on them. In addition, view indexes as essentially tables of primary keys.

Though indexes accelerate searches or SELECT statements, they make insertions and updates drag due to performing the operations on both the tables and the indexes.

Create an Index

You can create an index through a CREATE TABLE…INDEX statement or a CREATE INDEX statement. The best option supporting readability, maintenance, and best practices is CREATE INDEX.

Review the general syntax of Index given below −

CREATE [UNIQUE or FULLTEXT or...] INDEX index_name ON table_name column;

Review an example of its use −

CREATE UNIQUE INDEX top_sellers ON products_tbl product;

Drop an Index

You can drop an index with DROP INDEX or ALTER TABLE…DROP. The best option supporting readability, maintenance, and best practices is DROP INDEX.

Review the general syntax of Drop Index given below −

DROP INDEX index_name ON table_name;

Review an example of its use −

DROP INDEX top_sellers ON product_tbl;

Rename an Index

Rename an index with the ALTER TABLE statement. Review its general syntax given below −

ALTER TABLE table_name DROP INDEX index_name, ADD INDEX new_index_name;

Review an example of its use −

ALTER TABLE products_tbl DROP INDEX top_sellers, ADD INDEX top_2016sellers;

Managing Indexes

You will need to examine and track all indexes. Use SHOW INDEX to list all existing indexes associated with a given table. You can set the format of the displayed content by using an option such as “G”, which specifies a vertical format.

Review the following example −

mysql > SHOW INDEX FROM products_tblG

Table Statistics

Indexes are used heavily to optimize queries given the faster access to records, and the statistics provided. However, many users find index maintenance cumbersome. MariaDB 10.0 made storage engine independent statistics tables available, which calculate data statistics for every table in every storage engine, and even statistics for columns that are not indexed.

Transactions are sequential group operations. They function as a single unit, and do not terminate until all operations within the group execute successfully. A single failure in the group causes the entire transaction to fail, and causes it to have no impact on the database.

Transactions conform to ACID (Atomicity, Consistency, Isolation, and Durability) −

• Atomicity − It ensures the success of all operations by aborting on failures and rolling back changes.

• Consistency − It ensures the database applies changes on a successful transaction.

• Isolation − It enables independent transactions operation of transactions.

• Durability − It ensures the persistence of a successful transaction in the event of system failure.

At the head of a transaction statement is the START TRANSACTION statement followed by COMMIT and ROLLBACK statements −

• START TRANSACTION begins the transaction.

• COMMIT saves changes to data.

• ROLLBACK ends the transaction, destroying any changes.

On a successful transaction, COMMIT acts. On a failure, ROLLBACK acts.

Note − Some statements cause an implicit commit, and they also cause an error when used within transactions. Examples of such statements include, but are not limited to CREATE, ALTER, and DROP.

MariaDB transactions also include options like SAVEPOINT and LOCK TABLES. SAVEPOINT sets a restore point to utilize with ROLLBACK. LOCK TABLES allows controlling access to tables during sessions to prevent modifications during certain time periods.

The AUTOCOMMIT variable provides control over transactions. A setting of 1 forces all operations to be considered successful transactions, and a setting of 0 causes persistence of changes to only occur on an explicit COMMIT statement.

Structure of a Transaction

The general structure of a transaction statement consists of beginning with START TRANSACTION. The next step is inserting one or more commands/operations, inserting statements that check for errors, inserting ROLLBACK statements to manage any errors discovered and finally inserting a COMMIT statement to apply changes on successful operations.

Review the example given below −

START TRANSACTION;
SELECT name FROM products WHERE manufacturer = ''XYZ Corp
UPDATE spring_products SET item = name;
COMMIT;

Beyond the pattern matching available from LIKE clauses, MariaDB offers regular expression-based matching through the REGEXP operator. The operator performs pattern matching for a string expression based on a given pattern.

MariaDB 10.0.5 introduced PCRE Regular Expressions, which dramatically increases the scope of matching into areas like recursive patterns, look-ahead assertions, and more.

Review the use of standard REGEXP operator syntax given below −

SELECT column FROM table_name WHERE column REGEXP ''[PATTERN]

REGEXP returns 1 for a pattern match or 0 in the absence of one.

An option for the opposite exists in the form of NOT REGEXP. MariaDB also offers synonyms for REGEXP and NOT REGEXP, RLIKE and NOT RLIKE, which were created for compatibility reasons.

The pattern compared can be a literal string or something else such as a table column. In strings, it uses C escape syntax, so double any “” characters. REGEXP is also case-insensitive, with the exception of binary strings.

A table of possible patterns, which can be used are given below −

Review the pattern matching examples given below −

Products starting with “pr” −

SELECT name FROM product_tbl WHERE name REGEXP ''^pr

Products ending with “na” −

SELECT name FROM product_tbl WHERE name REGEXP ''na$

Products starting with a vowel −

SELECT name FROM product_tbl WHERE name REGEXP ''^[aeiou]