MySQL
MySQL introduction
MySQL was originally an open source relational database management system developed by Swedish company MySQL AB, which was acquired by Sun Microsystems in 2008. In 2009, Oracle acquired Sunrise Microsystems, and MySQL became Oracle's product.
MySQL has become the most popular open source database in the past because of its high performance, low cost and good reliability, so it is widely used in small and medium-sized websites on the Internet. With the maturity of MySQL, it is also gradually used in more large-scale websites and applications, such as Wikipedia, Google and Facebook. The "M" in the popular open source software combination LAMP refers to MySQL.
But after Oracle's acquisition, Oracle raised the price of MySQL's commercial version dramatically, and Oracle no longer supported the development of OpenSolaris, another free software project. As a result, the free software community is worried about whether Oracle will continue to support MySQL's social version (the only free version of MySQL). Michael Videius, founder of MySQL, is based on MySQL. Basically, a branch project MariaDB was established. The original open source software using MySQL has gradually turned to MariaDB or other databases. Wikipedia, for example, officially announced in 2013 that it would migrate from MySQL to MariaDB database [6].
Relational database
Relational database (English: Relational database) is a database built on the basis of relational model, which processes data in database by means of mathematical concepts and methods such as set algebra. Relational models are used to represent all kinds of entities in the real world and their relationships. The relational model was first proposed by Edgar Code in 1970, and it cooperated with "Code's 12 laws". Nowadays, although there are some criticisms about this model, it is still the traditional standard of data storage. Standard Data Query Language (SQL) is a language based on relational database, which performs data retrieval and operation in relational database.
The relational model consists of three parts: relational data structure, relational operation set and relational integrity constraints.
MySQL characteristics
MySQL is a widely used database with the following features:
- Using C and C++ to write and use a variety of compilers to test, 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.
- API is provided for many programming languages. Programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl.
- Supporting multi-threading and making full use of CPU resources
- Optimized SQL Query Algorithms to Improve Query Speed Effectively
- It can be applied as a separate application in the client server network environment as well as a library.
Embedded in other software to provide multilingual support, common encoding such as Chinese GB 2312, BIG5, Japanese Shift_JIS can be used as data table name and data column name.
- Provide TCP/IP, ODBC, JDBC and other database connection channels
- Provide management tools for managing, checking, and optimizing database operations
- Can handle large databases with tens of millions of records
Application of MySQL
Compared with large databases such as Oracle, DB2 and SQL Server, MySQL has its own shortcomings, such as small scale and limited functionality (MySQL Cluster's functions and efficiency are relatively poor), but this has not reduced its popularity in any way. For general users and small-medium-sized enterprises, MySQL provides moreAn essential functionality, and because MySQL is open source software, it can greatly reduce the overall cost of ownership. The prevalent Web site architecture on the Internet is LAMP(Linux+Apache+MySQL+PHP). Even with 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 free or open source software (FLOSS), using LAMP can build a stable and free website system without spending a penny.
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 and whether to use transactions according to the needs of applications. MySQL supports a variety of storage engines by default to meet the needs of database applications in different fields. Users can choose to use different storage engines to improve the efficiency of the application and provide flexible storage. User settings can customize and use their own storage engine according to their needs to achieve maximum customizability.
MySQL commonly used storage engines for MyISAM, InnoDB, MEMORY, MERGE, InnoDB provides transaction security tables, other storage engines are non-transaction security tables.
MyISAM is MySQL's default storage engine. MyISAM does not support transactions or foreign keys, but it has fast access speed and no requirement for transaction integrity.
InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. But InnoDB writes are less efficient than MyISAM storage engines and take up more disk space to keep data and indexes. MySQL supports only InnoDB as a foreign key storage engine. When creating foreign keys, the attached tables must have corresponding indexes. Subtables also automatically create corresponding indexes when creating foreign keys.
- MySQL Storage Engine Features
It is mainly embodied in performance, transaction, concurrency control, referential integrity, caching, fault recovery, backup and recall, etc.
At present, MyISAM and InnoDB are popular storage engines, and MyISAM is the first choice for most Web applications. The main difference between MyISAM and InnoDB is in performance and transaction control.
MyISAM is an extended implementation of early ISAM(Indexed Sequential Access Method). ISAM is designed to deal with situations where the read frequency is much higher than the write frequency. Therefore, ISAM and later MyISAM do not consider support for things, do not need transaction records. ISAM's query efficiency is considerable, and memory usage is very 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 locks are provided for concurrency control. Moreover, since MyISAM uses separate storage files (MYD data files and MYI index files) for each table, backup and recovery are very convenient (copy coverage can be done), and online recovery is also supported.
So if the application does not need transactions, does not support foreign keys, and only deals with basic CRUD operations, then MyISAM is the second choice.
Install MySQL 8.0 under Linux (CentOS 7.5_x86_64)
# Download mysql $ wget 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 # Modify 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 mysql groups $ groupadd mysql # Create 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 reporting bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory # Solution yum install -y libaio # Initialization error reporting 2018-07-08T02:53:24.542370Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc) starting as process 17745 ... mysqld: Can't create/write to file '/tmp/mysql/data/ibd35qXQ' (Errcode: 13 - Permission denied) 2018-07-08T02:53:24.554816Z 1 [ERROR] [MY-011066] InnoDB: Unable to create temporary file; errno: 13 2018-07-08T02: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 start up again. 2018-07-08T02:53:24.555000Z 0 [ERROR] [MY-010020] Data Dictionary initialization failed. 2018-07-08T02:53:24.555033Z 0 [ERROR] [MY-010119] Aborting 2018-07-08T02:53:24.555919Z 0 [System] [MY-010910] /usr/local/mysql/bin/mysqld: Shutdown complete. # Solution: Modify directory permissions for / tmp/mysql $ chown -R mysql:mysql /tmp/mysql # Successful initialization > If there are no exceptions, the log can be seen as follows mysql Default generates root Account number and password root@localhost: /TI(mjVAs1Ta [root@localhost mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 2019-01-29T10:19:34.023997Z 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 2019-01-29T10:19:43.041419Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server has completed # Copy mysql startup file to system initialization directory $ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld # Start mysql server $ service mysqld start
mysql basic operation
# Connecting mysql with mysql client $ /usr/local/mysql/bin/mysql -u root -p password //Modify mysql's default initialization password > alter user 'root'@'localhost' identified by 'root'; # Create user CREATE USER'User Name'@'Host Name' INDENTIFIED BY'User Password' > create user 'jack'@'localhost' identified by 'jack'; # grant permission on database. Table to'user name'@'login host' [INDENTIFIED BY'user password']; > grant replication slave on *.* to 'jack'@'localhost'; # Refresh # $ flush privileges; # Modified root users can connect remotely > update mysql.user set host='%' where user='root'; # View users for mysql > select user,host from mysql.user; # Maximum number of connections docker modifies mysql apt-get update apt-get install vim vim /etc/mysql/mysql.conf.d/mysqld.cnf max_connections=1000 > alter user 'root'@'%' identified with mysql_native_password by 'root';
Master-slave replication of mysql Cluster
Prepare two installed mysql servers
192.168.79.15 (master)
192.168.79.16 (slave)
# Configure the main service to add the following configuration $ vim /etc/my.cnf # Node unique id value server-id=1 # Open binary logs log-bin=mysql-bin # The specified log format is mixed | row | state recommended mixed binlog-format=mixed # Step value auto_imcrement. Normally, if there are n hosts, MySQL will fill in n (optional configuration) auto_increment_increment=2 # Starting value. Normally fill in the number n host MySQL. This is the first master MySQL (optional configuration) auto_increment_offset=1 # Ignore the mysql library (optional configuration) binlog-ignore=mysql # Ignore the information_schema library (optional configuration) binlog-ignore=information_schema # To synchronize databases, default all databases (optional configuration) replicate-do-db=db1 # slave node configuration # Node unique id value server-id=2 # Open binary logs log-bin=mysql-bin # Step value auto_imcrement. Normally, if there are n hosts, MySQL will fill in n (optional configuration) auto_increment_increment=2 # Starting value. Normally fill in the number n host MySQL. This is the first master MySQL (optional configuration) auto_increment_offset=2 # To synchronize databases, default all databases (optional configuration) replicate-do-db=db1 # View the status 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 | | | | +------------------+----------+--------------+------------------+-------------------+ # Execute the following commands at the slave node //Note that master_log_file corresponds to show master status; the value of file in master_log_pos corresponds to position. > 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; # View the status of slave > show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master 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 has read 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 # When Slave_IO_Running: Yes and Slave_SQL_Running: Yes are yes, it means that master-slave replication is normal. #Reset the slave state. $ reset slave; #Suspend slave status; $ stop slave;