Shulou(Shulou.com)06/01 Report--

MySQL

Introduction to MySQL

MySQL was originally an open source relational database management system developed by Sweden's MySQL AB, which was acquired by Shenyang Microsystems (Sun Microsystems) in 2008. In 2009, Oracle acquired Shenyang Microsystems, and MySQL became a product of Oracle.

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 Internet. As MySQL continues to mature, it is also gradually used in more large-scale websites and applications, such as Wikipedia, Google and Facebook. The "M" in the very popular open source software combination LAMP refers to MySQL.

But after it was acquired by Oracle, Oracle sharply increased the price of MySQL's commercial version, and Oracle no longer supported the development of OpenSolaris, another free software project. As a result, the free software community was worried about whether Oracle would continue to support the MySQL community version (the only free version of MySQL). MySQL founder Michael Vidnius set up a branch plan MariaDB based on MySQL. However, some open source software that used to use MySQL 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 created on the basis of relational model, which deals with the data in the database with the help of mathematical concepts and methods such as set algebra. All kinds of entities in the real world and the relationships between them are represented by relational models. The relation model was first put forward by Edgar Cod in 1970 and cooperated with the Twelve laws of Cod. Although there are some criticisms of this model nowadays, it is still the traditional standard of data storage. The standard data query language SQL is a language based on relational database, which performs the retrieval and operation of data 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:

Written in C and C++, and tested with a variety of compilers to ensure the portability of the source code    supports AIX, FreeBSD, HP-UX, Linux, Mac OS, Novell Netware, OpenBSD, OS/2Wrap, Solaris, Windows and other operating systems.    provides API for many programming languages. Programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl.    supports multithreading and makes full use of CPU resources    optimized SQL query algorithm, which can effectively improve the query speed not only as a separate application in the client server network environment, but also as a library embedded in other software to provide multilingual support, common codes such as Chinese GB 2312, BIG5 Japanese Shift_JIS can be used as data table name and data column name to provide a variety of database connections such as TCP/IP, ODBC and JDBC to provide management tools for managing, checking and optimizing database operations. It can handle large databases with tens of millions of records.

MySQL application

Compared with large databases such as Oracle, DB2, SQL Server, MySQL has its own shortcomings, such as small scale, limited function (MySQL Cluster's function and efficiency are relatively poor), but this does not reduce its popularity at all. MySQL provides more than enough functionality for the average individual user and small and medium-sized business, and because MySQL is open source software, it can greatly reduce the total cost of ownership. At present, the popular way of website construction on Internet is LAMP (Linux+Apache+MySQL+PHP), even if Linux is used 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 is free or open source software (FLOSS), you can build a stable, free website system using LAMP without spending a penny.

MySLQ storage engine

Introduction to MySQL Storage Engin

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 the application. 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 applications and provide flexible storage. Users can customize and use their own storage engines according to their own needs to achieve maximum customization.

The commonly used storage engines of MySQL are MyISAM, InnoDB, MEMORY and MERGE, in which InnoDB provides transaction security tables, and other storage engines are non-transaction security tables.

MyISAM is the default storage engine for MySQL. MyISAM does not support transactions and foreign keys, but its access speed is fast and there is no requirement for transaction integrity.

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. But compared to the MyISAM storage engine, InnoDB writes are less efficient and take up more disk space to retain data and indexes. MySQL supports only InnoDB in the foreign key storage engine. When creating a foreign key, the schedule must have a corresponding index, and the child table will automatically create the corresponding index when creating the foreign key.

MySQL storage engine features

It is mainly reflected in performance, transaction, concurrency control, referential integrity, caching, fault recovery, backup and save back.

At present, the popular storage engines are MyISAM and InnoDB, and MyISAM is the first choice for 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 deal with situations where the frequency of reading is much greater than that of writing, so ISAM and later MyISAM do not consider the support for things, do not need transaction records, and the query efficiency of ISAM is considerable, and takes up very little memory.

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.

And because MyISAM uses its own independent storage files (MYD data files and MYI index files) for each table, it makes backup and recovery very convenient (copy overwrite), and also supports online recovery.

So if the application does not need transactions, does not support foreign keys, and only deals with basic CRUD (add, delete, change and search) operations, then MyISAM is the best choice.

Install mysql8.0 under linux (CentOS7.5_x86_64)

# download mysql $wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.13-el7-x86_64.tar.gz#, extract $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 to add default configuration File $vim/etc/ my.cnf [client] port=3306socket=/tmp/ mysql.sock [mysqld] port=3306user=mysqlsocket=/tmp/mysql.sockbasedir=/usr/local/mysqldatadir=/usr/local/mysql/data# create mysql group $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 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 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: 132018-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] Aborting2018-07-08T02:53:24.555919Z 0 [System] [MY-010910] / usr/local/mysql/bin/mysqld: Shutdown complete.# solution: modify the directory permissions of / tmp/mysql $chown-R Mysql:mysql / tmp/mysql# initialization succeeded > if there is no exception log, you can see below that mysql generates root account and password root@localhost: / TI (mjVAs1Ta[ root @ localhost mysql] # bin/mysqld-- initialize-- user=mysql-- basedir=/usr/local/mysql-- datadir=/usr/local/mysql/data2019-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 42402019-01-29T10:19:39.764895Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: / TI (mjVAs1Ta2019-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 Start the mysql server $service mysqld start in the directory $cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld#

Basic operation of mysql

# use mysql client connection mysql$ / usr/local/mysql/bin/mysql-u root-p password to modify the default initialization password of mysql > alter user 'root'@'localhost' identified by' root';# to create user CREATE USER 'user name' @ 'host name' INDENTIFIED BY 'user password' > create user 'jack'@'localhost' identified by' jack';# to grant grant permissions to the on database. Table to 'username' @ 'login host' [INDENTIFIED BY 'user password']; > grant replication slave on *. * to 'jack'@'localhost';# refresh # $flush privileges; # modify root users can connect remotely > update mysql.user set host='%' where user='root';# to view users used by mysql > select user,host from mysql.user # docker modifies the maximum number of connections of mysql apt-get updateapt-get install vimvim / etc/mysql/mysql.conf.d/mysqld.cnfmax_connections=1000 > alter user 'root'@'%' identified with mysql_native_password by' root'

Mysql cluster master-slave replication

Prepare two installed mysql servers

# configure main service add the following configuration $vim / etc/my.cnf# node unique id value server-id=1# enable binary log log-bin=mysql-bin# specified log format is mixed | row | statement recommends mixedbinlog-format=mixed# step value auto_imcrement. Generally, if there are n master MySQL, enter the starting value of n (optional configuration) auto_increment_increment=2 #. Generally fill in the nth main MySQL. At this time, for the first primary MySQL (optional configuration) auto_increment_offset=1 # ignores the mysql library (optional configuration) binlog-ignore=mysql # ignores the database to be synchronized with the information_schema library (optional configuration) replicate-do-db=db1# slave node configuration # node unique id value server-id=2# enables binary log log-bin=mysql-bin# step value auto_imcrement. Generally, if there are n master MySQL, enter the starting value of n (optional configuration) auto_increment_increment=2#. Generally fill in the nth main MySQL. This is the database to be synchronized by the first master MySQL (optional configuration) auto_increment_offset=2#. By default, all libraries (optional configuration) replicate-do-db=db1# check the status of 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 command on the slave node. Note that master_log_file corresponds to show master status. The value of file in master_log_pos, which corresponds to the value of position > change master tomaster_host='192.168.79.15',master_user='root',master_password='root',master_log_file='mysql-bin.000009',master_log_pos=0;# startup slave status (start listening for changes in msater) > start slave # View the status of slave > show slave status\ gateway * 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 : 0Master_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: when Slave _ IO_Running: Yes and Slave_SQL_Running: Yes are both yes, which means that master-slave replication is normal. # reset slave status. $reset slave # pause slave status; $stop slave

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.