Configuration and management of MARIADB database

A MySQL profile

1 relational database
Representative Product: MySQL Oracle SQL Server
2 Non-relational database (NoSQL)
On behalf of the product: MongoDB memcache Redis, Cassandra HBASE couchdb
The history of MySQL development
1 MySQL Introduction
2 difficult births
3 Seeking Development
4 giants Rise

Simple comparison of MySQL with other databases
1 function comparison
2 Ease of Use comparison
3 Performance Comparison
4 Reliability

Stable performance with minimal downtime
MySQL open source and no version constraints, autonomy and low cost of use
MySQL has a long history, community and users are very active,
MySQL software is small, easy to install, easy to maintain, low cost of installation and maintenance
MySQL brand reputation effect, so that enterprises do not have to consider the direct use of LAMP,LNMP popular architecture
MySQL supports multiple operating systems, provides multiple API interfaces, and supports multiple development languages

Main usage scenarios for MySQL
1 Web Site System
2 Logging System
3 Data Warehouse System
4 Embedded Systems

MySQL database classification and version upgrade
MySQL Database's official website
Http:www.mysql.com

Its release of the MySQL version of the dual licensing policy, and most of the open source products, the same as the community and commercial version, and these two versions are divided into four versions of the release

The alpha version, beat version, RC version, GA version (can be stabilized on-line version)

MySQL Business Edition is published internally by MySQL company, while referring to the community version of the initiative code features and algorithms, is the MySQL company's profitable products, need to pay to provide services,

MySQL Community edition is distributed around the world by MySQL developers, enthusiasts and users to participate in development and testing, and complete the management of software code, testing work, the community will also set up a bug reporting mechanism, mobile phone users encounter bug situation, compared to the commercial version, the community version of the development and testing environment is not so strict , comply with the GPL agreement

MySQL Mainstream version of the branch

1 MARIADB is one of the important branches of MySQL, fully compatible with MySQL, easy to be a replacement for MySQL, introducing many new features, multiple replication Galera cluter clusters, tokudb storage engine, etc.

2 Percona server based on the InnoDB storage Engine enhancement, formed the xtradb, can better play the performance advantage of the machine, there are many tools to use, convenient for our team database operation and maintenance work
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Two downloads and installation

1:https://cdn.mysql.com//downloads/mysql-5.6/mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz

2 installation
Unzip to the specified folder:

Modify file name and authorization

Create a data file directory and authorize

Creating a configuration file

[client] MySQL client configuration file
Port = 3306
Socket =/tmp/mysql.sock
Default-character-set=utf8
[MySQL]
Default-character-set=utf8 MySQL Connection configuration file
Port = 3306 Port number
Socket =/tmp/mysql.sock (MySQL has two connection methods, one is socket connection, One is a network connection) This parameter is generated when MySQL is started.
Basedir MySQL's home directory
datadir mysql data directory
Open_file_limit = 3072 Open File Restrictions
Back_log =103
max_connections= 512 maximum number of connections for a database
max_connect_errors=100000 connection error value
table_open_cache=512 Open the cache for the table
External-locking=false
Max _allowed_packet=128m the size of the pass-through package
Sort_buffer_size=2m can be adjusted in multiples of 2
join_buffer_size=2m The post of the connection can be adjusted by a multiple of 2
thread_cache_size=51
query_cache_size=32m Cache only static data
tmp_table_size=96m the size of the temporary tablespace, the
Max_heap_table used when memory is insufficient _size=96m Stack table

Slow Query Series:
Slow_query_log=1 Slow Query
Slow_query _log_file= Slow query location in general, the log files are in the data directory
Log_query_time= 0.05 Slow query time (in seconds)
Server-id = 3 (Unique identification of this device, used to build the main preparation)
Binary part:
log_bin= MySQL binary log location
Sync_binlog=1 when the binary is refreshed (real-time refresh) 0 is given to the system to refresh, when the NN transactions are refreshed.
binlog_cache_size=4m Allocation of binglog size
max_binlog_cache_size=128m Maximum cannot exceed
max_binlog_size=1024m
Expire_logs_days=7 the expiration time of the binary log
Format of the Binlog_format=row binary
3 format:
1 Statement Record SQL statements, database migration is easy to lose data
2 row * exists in line record mode
3 Mixed over version

The following 3 are all configurations related to MyISAM
key_buffer_size=32m
read_buffer_size=1m
read_rnd_buffer_size=16m

bulk_insert_buffer_size=64m size of the insert cache
Character-set-server=utf8 the character set of the database
Default-storage-engine=innodb The default storage engine for the database

interactive_timeout=100 Interactive Wait time unit is s
wait_timeout=100 non-interactive wait time unit is s
To conserve memory resources in the database, the connection time expires automatically (100-500)

Transaction_isolation=repeatable-read
innodb_additional_mem_pool_size=16m
innodb_buffer_pool_size=1434m set to 50%-80% of physical memory
Innodb_data_file_path= ibdata1:1024m:autoextend sharing tablespace files
Innodb_flush_log_at_trx_commit=1 Redo Log Refresh condition (0 for every 1 seconds to refresh, 1 for real-time refresh, 2 for the OS refresh), at this time to refresh to the file, Flush to disk one is trigger refresh (trigger to flash memory data into file, time)
innodb_log_buffer_size=16m size of redo log buffer
innodb_log_file_size=256m size of redo on disk
innodb_log_files_in_group=2 redo files in a group
INNODB_MAX_DIRTY_PAGES_PCT=50 Innodb Conditions (scale) for dirty page refreshes
Innodb_file_per_table=1 Open Innodb stand-alone table space, not before 5.6, 5.6 is a separate tablespace, the basic information of the table is still in ibdata1
innodb_locks_unsafe_for_binlong=0 Turn on unsafe mode in repeat Read
[MySQL dump]
Quick
max_allowed_packet=32m

By default: Skip-networking=1 is turned on, and you can turn off network ports with this command
Security settings for MySQL
To set the initial password for MySQL:
Mysql_securce_installation

To modify the MySQL password:

Configuration in case of loss of the password of the user

MySQL Service initialization:

To turn on the MySQL service:

Check the installation situation:

At this point, installation and completion
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Three basic configuration settings MySQL user name and password and permissions

To view the creation and management of the default libraries and libraries in the database

Create a data table and view

View table Structure

Changes to the data:

Inserting data

Add columns

To add a column to a specified location

To modify a column property:

Automatic update of the Append Time column:

Modify Table Name:

Deletion of data

Delete data from a table

Delete a table

Deleting a database

To delete a column:

Delete data:

Create a Telnet user

View user information and status:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Four database backup:

1 Back up all data in the library, including table data and table structure

2 backup table structure only:

3 backing up the entire database

3 Backing up the data structure of the entire database without backing up data

Recovery operations for the database:


Recovery of data through original backed-up packets

Configuration and management of MARIADB database