MySQL
Introduction of MySQL
MySQL was originally an open source relational database management system developed by MySQL AB, a Swedish company that was acquired by Sun Microsystems in 2008. In 2009, Oracle acquired Sun Microsystems, and MySQL became an Oracle product.
MySQL has become the most popular open source database in the past due to its high performance, low cost and good reliability, so it is widely used in small and medium-sized websites on the Internet. As MySQL matures, it is increasingly being used on more large-scale sites and applications, such as Wikipedia, Google and Facebook. The “M” in LAMP, a popular open-source software portfolio, refers to MySQL.
But Oracle raised the price of its commercial version of MySQL sharply after it was acquired by Oracle, and Oracle stopped supporting another free software project, OpenSolaris, As a result, the free software community wondered whether Oracle would continue to support MySQL Community Edition (the only free version of MySQL). MySQL founder Michael Widenius formed MariaDB, a branch project based on MySQL. Some of the open source software that used MySQL is moving to MariaDB or other databases. For example, Wikipedia officially announced in 2013 that it would migrate from MySQL to MariaDB database [6].
Relational database
Relational Database (English: Relational Database) is a database created on the basis of Relational model, with the help of mathematical concepts and methods such as set algebra to process the data in the database. The various entities in the real world and the various relationships among them are represented by relational models. The relational model was first proposed by Edgar Coad in 1970, in conjunction with the “Coad twelve Laws”. Although this model has its critics today, it is the traditional standard for data storage. The standard data query language SQL is a relational database based language, which performs the retrieval and manipulation of data in a relational database.
Relational model consists of relational data structure, relational operation set and relational integrity constraint.
MySQL feature
MySQL is a widely used database with the following features:
-
Written in C and C++, and using a variety of compilers for testing, to ensure the portability of source code
-
Support AIX, FreeBSD, HP-UX, Linux, Mac OS, Novell Netware, OpenBSD, OS/2 Wrap, Solaris, Windows and other operating systems.
-
Provides apis for a variety of programming languages. Programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl.
-
Support multithreading, make full use of CPU resources
-
Optimized SQL query algorithm, effectively improve query speed
-
It can not only be used as a single application in the client server network environment, but also be embedded in other software as a library to provide multi-language support. Common codes such as GB 2312 in Chinese, BIG5, Shift_JIS in Japanese, etc., can be used as data table names and data column names
-
Provides TCP/IP, ODBC, JDBC, and other database connections
-
Provides management tools for managing, checking, and optimizing database operations
-
Can handle large databases with tens of millions of records
MySQL application
Compared with large databases such as Oracle, DB2, SQL Server, etc., MySQL has its own shortcomings, such as small scale, limited functions (MySQL Cluster is relatively poor in function and efficiency), but this does not reduce its popularity. MySQL already provides more than enough functionality for average individual users and small to medium businesses, and because MySQL is open source software, it can significantly reduce the total cost of ownership. At present, LAMP (Linux+Apache+MySQL+PHP) is the popular website architecture on the Internet, which uses Linux as the operating system, Apache as the Web server, MySQL as the database, and PHP as the server-side script interpreter. Because Linux+Apache+MySQL+PHP are all free or open source software (FLOSS), LAMP does not cost a penny to build a stable, free web system.
MySLQ storage engine
- Introduction to MySQL Storage engine
Plug-in storage engine is one of the most important features of MySQL database. Users can choose how to store and index the database according to the needs of the application, whether to use transactions, etc. By default, mySQL supports multiple storage engines to meet the requirements of database applications in different fields. You can choose different storage engines to improve application efficiency and provide flexible storage. You can customize and use your own storage engines as required to achieve maximum customization. MySQL commonly used storage engines are MyISAM, InnoDB, MEMORY, MERGE. InnoDB provides transaction-safe tables, while other storage engines are non-transaction-safe tables. MyISAM is the default storage engine for MySQL. MyISAM does not support transactions, nor does it support foreign keys, but it has fast access and no transaction integrity requirements. InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. But InnoDB writes less efficiently than the MyISAM storage engine and takes up more disk space to retain data and indexes. MySQL supports foreign key storage engine only InnoDB. When creating a foreign key, the table must have the corresponding index, and the sub-table will automatically create the corresponding index when creating a foreign key.
- MySQL storage engine features
At present, MyISAM and InnoDB are popular storage engines, which are mainly reflected in performance, transaction, concurrency control, referential integrity, cache, fault recovery, backup and recall. MyISAM is the first choice of most Web applications. The main differences between MyISAM and InnoDB are performance and transaction control. MyISAM is an extended implementation of early ISAM(Indexed Sequential Access Method). ISAM is designed to handle the situation where the read frequency is much higher than the write frequency. Therefore, ISAM and its subsequent MyISAM do not consider the support of things and do not require transaction records. The query efficiency of ISAM is considerable, and the memory footprint is small. While inheriting the advantages of ISAM, MyISAM provides a large number of practical new features and related tools with The Times. For example, table level locking is provided for concurrency control. And because MyISAM is a separate storage file for each table (MYD data file and MYI index file), it is very easy to backup and restore (copy and overwrite), and also supports online recovery. So if your application doesn’t need transactions, doesn’t support foreign keys, and just handles basic CRUD operations, MyISAM is a good choice.
Install mysql8.0 under Linux (Centos7.5_x86_64)
# download mysql$wget HTTP: / / http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.13-el7-x86_64.tar.gz# decompression$mysql tar -zxvf mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz -c /usr/local
# Change the folder name$mv mysql-8.0.4-rc-linux-glibc2.12-x86_64/ mysql add default configuration file $vim/etc/my.cnf [client] port=3306 socket=/ TMP /mysql.sock [mysqld] port=3306 user=mysql socket=/tmp/mysql.sock basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
Create a mysql group
$ groupadd mysql
Create a mysql user
$ useradd -g mysql mysql
Create mysql data directory
$ mkdir $MYSQL_HOME/data
Initialize mysql
$ /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
Initialization error reported
bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
# Solutions
yum install -y libaio
Initialization error reported2018-07-08T2:53:24.542370z 0 [System] [my-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc) mysqld: Can't create/write to file '/tmp/mysql/data/ibd35qXQ'2018-07-08t2:53:24.554816z 1 [ERROR] [my-011066] InnoDB: Unable to create temporary file; Errno: 13 2018-07-08T2:53:24.554856z 1 [ERROR] [my-011066] InnoDB: InnoDB Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to 2018-07-08T02:53:24.555000z 0 [ERROR] [my-010020] Data Dictionary initialization failed 2018-07-08T2:53:24.555033z 0 [ERROR] [my-010119] Aborting 2018-07-08T2:53:24.555919z 0 [System] [my-010910] 2018-07-08T2:53:24.555033z 0 [ERROR] [My-010119] Aborting 2018-07-08T2:53:24.555919z 0 /usr/local/mysql/bin/mysqld: Shutdown complete. $chown -r mysql:mysql/TMP /mysql $chown -r mysql:mysql/TMP /mysql /TI(mjVAs1Ta [root@localhost mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql - datadir = / usr/local/mysql/data 2019-01-29 T10: when the 023997 z 0 [System] [MY - 013169] / Server/usr/local/mysql/bin/mysqld (mysqld 8.0.13) Initializing of server in Progress as Process 4240 2019-01-29T10:19:39.764895z 5 [Note] [my-010454] [Server] A temporary password is generated for root@localhost: / TI (mjVAs1Ta T10:2019-01-29 parts. 041419 z 0 [System] [MY - 013170] / Server/usr/local/mysql/bin/mysqld (mysqld 8.0.13) Initializing of server has completed # Copy the mysql startup file to the system initialization directory $cp /usr/local/mysql.supp-files /mysql.server /etc/init.d/mysqld $service mysqld startCopy the codeMysql > alter database
Use mysql client to connect to mysql
$ /usr/local/mysql/bin/mysql -u root -p password Change the default initial password of mysql > alter user'root'@'localhost' identified by 'root';
CREATE USER 'username '@' hostname' INDENTIFIED BY 'username'
> create user 'jack'@'localhost' identified by 'jack';
Grant permission on the database. Table to 'username '@' login host' [INDENTIFIED BY 'username '];
> grant replication slave on *.* to 'jack'@'localhost';
# refresh
# $ flush privileges;
The root user can connect remotely
> update mysql.user set host=The '%' where user='root';
Mysql > select * from user
> select user,host from mysql.user;
# docker change the maximum number of connections in mysql
apt-get update
apt-get install vim
vim /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections=1000
> alter user 'root'@The '%' identified with mysql_native_password by 'root';
Copy the codeMysql cluster primary/secondary replication
Prepare two mysql servers. 192.168.79.15 (master) 192.168.79.16 (slave)
Add the following configuration to the master service
$ vim /etc/my.cnf
# Unique id of the node
server-id=1
Enable binary logging
log-bin=mysql-bin
# specified log format Recommend mixed with mixed | row | statement
binlog-format=mixed
# step value auto_imcrement If there are n primary MySQL servers, fill in n(optional)
auto_increment_increment=2
# Start value. MySQL > select MySQL from NTH host. This is the first primary MySQL(optional)
auto_increment_offset=1
# ignore mysql library (optional configuration)
binlog-ignore=mysql
# Ignore information_SCHEMA libraries (optional)
binlog-ignore=information_schema
# database to synchronize, default all libraries (optional configuration)
replicate-do-db=db1
Configure the slave node
# Unique id of the node
server-id=2
Enable binary logging
log-bin=mysql-bin
# step value auto_imcrement If there are n primary MySQL servers, fill in n(optional)
auto_increment_increment=2
# Start value. MySQL > select MySQL from NTH host. This is the first primary MySQL(optional)
auto_increment_offset=2
# database to synchronize, default all libraries (optional configuration)
replicate-do-db=db1
Check the state of the master, especially the current log and location> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 1608 | | | | +------------------+----------+--------------+------------------+-------------------+# Run the following command on the slave nodeNote that master_log_file corresponds to show master status; Change master to master_host= change master to master_host='192.168.79.15',
master_user='root',
master_password='root',
master_log_file='mysql-bin.000009',
master_log_pos=0;
Start slave status (start listening for msater changes)
> start slave;
Check the status of the slave
> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting forMaster to send event Master_Host: 192.168.79.15 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 863 Relay_Log_File: node-6-relay-bin.000002 Relay_Log_Pos: 500 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 863 Relay_Log_Space: 709 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 Master_UUID: 6291c709-23af-11e9-99fb-000c29071862 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave hasread all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
# If Slave_IO_Running: Yes and Slave_SQL_Running: Yes are both set to Yes, master/slave replication is normal
Reset the slave state.
$ reset slave;
# suspend slave state
$ stop slave;
Copy the code