Creation of Mysql database

Question

I’m starting with Mysql database, and I’d like to ask you a few questions. I have been doing some research but still I could not resolve these doubts. They would be:

1. I noticed that in the creation of the table has an ENGINE - Innodb, what’s the point?

2. Are there any standards to follow when creating the database and its tables in Mysql? Default these as, set the charset, put this ENGINE in the tables, etc, so that in the future does not suffer/occur any problem if you need to perform something that was not changed at the beginning of the database creation.

3. Regarding Backup and Restore. It is a procedure done via programming according to the language you are using or is done on the machine from which the Mysql server is located?

4. If possible have a few more tips, opinions to keep the database consistent.

Answer 1

What Are Storage Engines?

Storage engines, or storage engines, are Mysql components that control SQL operations for different types of tables that store and manage information in a database. Innodb is the storage engine commonly used as a general purpose mechanism and from Mysql version 5.5 and later, it is the engine in the missing(default) or in the absence of a choice Innodb is the system’s default engine.

There are some storage engines in Mysql and they are used for different purposes:

Innodb

This is the default storage engine for Mysql 5.5 and above. It provides secure tables to transactions (ACID compliant), supports FOREIGN KEY referential integrity restrictions. It supports confirmation, reversal and fault recovery features to protect data. It also supports line-level locking. It "consistent nonlocking readings" increases performance when used in a multi-user environment. It stores data in clustered indices that reduce I/O of queries based on primary keys.

Myisam

These are tables of little impact on memory. Table-level locking limits performance on read/write workloads, so it is often used in read-only workloads or mainly read-only workloads in web and data storage settings. Each Myisam table is stored on disk in two files. The files have names that start with the table name and have an extension to indicate the file type. The data file has an extension. MYD (Mydata). The index file has an extension . MYI (Myindex). The table definition is stored in the Mysql data dictionary.

Memory

Stores all data in RAM for quick access in environments that require fast, non-critical data searches. This mechanism was previously known as the HEAP mechanism. Its use cases are decreasing; Innodb with its buffer pool memory area provides a general and durable way to keep most or all data in memory, and NDBCLUSTER provides quick value searches for huge distributed datasets.

CSV

Your tables are actually text files with comma-separated values. CSV tables allow importing or downloading data in CSV format to exchange data with scripts and applications that read and write in the same format. Since CSV tables are not indexed, you usually keep the data in Innodb tables during normal operation and only use CSV tables during the import or export stage.

Archive

These compact, unendexed tables are intended to store and retrieve large amounts of rarely referenced historical, archived or security audit information. The ARCHIVE engine supports INSERT, REPLACE and SELECT, but not DELETE or UPDATE. It supports ORDER BY operations, BLOB columns and spatial data types. Geographic spatial reference systems are not supported. The ARCHIVE engine uses the line-level lock.

Blackhole

Blackhole storage engine accepts but does not store data, similar to the Unix device /dev/null. Queries always return an empty set. These tables can be used in replication settings where DML instructions are sent to slave servers, but the master server does not keep its own copy of the data.

NDB

Also known as NDBCLUSTER, this is a cluster database engine and is particularly suitable for applications that require the highest possible degree of uptime and availability. It is a high availability and high redundancy version of the Mysql engine adapted for the distributed computing environment. Recent NDB Cluster release series use version 8 of the NDB storage engine to allow running multiple computers with Mysql servers and other software in a cluster.

Merge

Allows a DBA or Mysql developer to logically group a series of identical Myisam tables and refer to them as a single object. Good for VLDB environments such as data Warehousing.

Federated

Offers the ability to link separate Mysql servers to create a logical database from multiple physical servers. Very good for distributed or repository environments.

There’s still the guy Example which is just an example for developers to create their own mechanisms.

---

What is Charset?

Charset is the character encoding.

ASCII was the first character encoding standard (also called character set). ASCII defined 128 different alphanumeric characters that can be used on the Internet: numbers (0-9), English letters (A-Z), and some special characters such as! $ + - () @ <>.

ISO-8859-1 was the default character set for HTML 4. This character set also supported 256 different character codes.

ANSI (Windows-1252) was the original character set of Windows. ANSI is identical to ISO-8859-1, except that ANSI has 32 extra characters.

As ANSI and ISO-8859-1 were so limited, HTML 4 also supported UTF-8.

UTF-8 (Unicode) covers almost all characters and symbols in the world.

Today default character encoding for HTML5 is UTF-8.

---

Backup

As the third question, the backup can either be done on the machine or via script From a read on that question How to dump a Mysql database with PHP?.

---

A tip?

Keep reading always up to date.

Answer 2

Hello, the ideal is to separate the questions, because for each question there can be several explanations.

Simplifying everything, for simple systems, you can even forget the ENGINE, charset...

It will directly create the tables according to the server settings. I prefer UTF-8 in the database and pages and codes.

The backup is how to do in the shell using mysql-dump, in the site control panel, or you have a script that does this for you. Most servers have backup in the package. Except if you have a dedicated server without hiring a technician.

For consistency, setting the fields of agreements with the data, creating the relationships, and I prefer not to insert images in the database, only the "path" of location, so the database gets smaller and its function would be for data and not for storage. : P