MySQL initialization and use of client tools

MySQL initialization and use of client tools

Yun Zhengjie

Copyright Notice: Original works, declined reprint! Otherwise, the legal liability will be investigated.

I. What is a relational databaserelational databases are usually organized into two-dimensional relationships with all the data. It is called a relational database because he organizes the data in a two-dimensional relational table with fields and records. Besides, this relational table provides a lot of auxiliary tools to complete the maintenance of relational tables and data access functions. we know that if the data is stored as a character, there are text objects and binary large objects. Binary large objects are not stored directly in the database, but are placed on a path on the server, while the database stores the knowledge of the data pointer. For example, to access a high-definition wallpaper, it does not write the contents of the image directly into the database, but the absolute path of the image is stored in a field in the database. This relationship is what we call an object-oriented relational database. (RDBMS). Now many relational databases also support the direct interaction of XML-formatted interfaces.   two. Branch of a relational databasein the early 90, the relational database was three pillars by sybase,informix,oracle three companies. These three companies have different plans for the company, and Sybase and Informix plan are very forward-looking, and Oracle's goal is very accurate, is to do their products well, and finally Oracle continues to improve, it is clear that the most forward of course is Oracle. Where Sybase used to work with Microsoft, and for some reason Sybase withdrew from Microsoft, Microsoft finally developed its own SQL Server database, and Informix was acquired by IBM. Now Informix is just one of IBM's sub-project companies, and a typical database representative is DB2. early MySQL was AB and finally sold to Sun for $10, and unfortunately sun was acquired by Oracle, and Oracle's usual trick was to buy and limbo rivals. So in the face of capitalism to talk about ideals are moral doctrine. After Sun was acquired, MySQL was also owned by Oracle. Oracle, as it was expected, did not do it for 5 years at the time of the acquisition, but in fact MySQL and was ravaged countless times. So companies like Google, Facebook and others have moved from MySQL software to other software. And in the RHEL7 version of the default database has changed MySQL to MARIADB. MySQL's original author after sum company was acquired for a long time to separate the portal, on the basis of early-morning MySQL proposed another branch called MARIADB. In fact, MARIADB integrates the powerful technical power of the open source community on the basis of being compatible with MySQL. We know that MySQL got up early to integrate the storage engine one is InnoDB, and InnoDB belongs to Innobase company, Innobase Company was acquired by Oracle as early as 2008. Fortunately, there is always a lot of strange people in the open source world, such as the famous Percona company, which is committed to the optimization of MySQL has made a lot of achievements in the Organization, the organization in the early morning improvement of open source InnoDB based on the development of the enhanced version of the InnoDB, named Xtradb. As you can see, MARIADB's storage engine is the XTRADB. We can understand that MARIADB is a product of MySQL evolution. The world's leading internet companies, such as Google and Facebook, are bullish on mariadb and have started using the software. in fact, Google and Facebook and several other companies in the wake up early MySQL technology concept of different they also developed another Internet product called Webscaledb, a Web-specific database system. It softens the products of the technology of Mysql,mariadb,percona and innobase four companies. in the open source database product There is also an important product called PostgreSQL (Pgsql), in fact, the early name is called Egresql, but egresql in the commercial operation of the competition with Oracle completely defeated. That it has disappeared more early than Informix. In fact, in terms of relational databases, Pgsql is technically superior to many. But the market decides everything, so MySQL may be a lot of contacts with a more relational database. specify another relational database called SQLite, it is not the same as MySQL, we know that MySQL is based on the C/S architecture, the client and the server is to communicate through the MySQL protocol. And SQLite is a simple, working on-premises, non-service API of a relational database interface (its client and server is one). Note that SQLite is only an engine, SQLite is mainly used in the embedded platform and have to use the relational database interface of such scenarios. Again, SQLite is a non-c./s architecture, it is purely an engine API mechanism, the data behind is stored as simple files on the file system, do not do too much parsing, but can fully support our programmers to use Select,insert and other mechanisms of data to complete the persistence of.   three. Initialization of MySQLWe know that there are many ways to install MySQL, such as Rpm,yum, source installation, binary installation MySQL and so on. But either way installed MySQL we all need to do an operation is MySQL initialization. And the correct initial posture we are divided into two operations. First, provide the configuration file; Second, delete the anonymous user, and so on. 1. Provide configuration filesThe MySQL default profile is a file that ends with "*.cnf", which is a centralized configuration file and a configuration file that is shared by multiple applications. Of course, can also not play like this, later I will share the case to everyone. We can view some of MySQL's default information with a single command. That is "[[email protected] ~]# mysql--help--verbose" Yes, we can get a lot of output by this command:a> Displays the options that are available when the MYSQLD program is started, usually a long option;B> Displays the service variables available in the MYSQLD configuration file;C> Displays how the configuration file is used, that is: first, it looks for each file that needs to be looked up, and the result is all file-set; second, if a parameter appears multiple times in several files, the last read takes effect.  2. Resetting user InformationWe know that there are many default users on a newly installed server, such as root and some anonymous users, they are not password, so it is very insecure, so we need to delete unnecessary users, and we need to use the user set the password and authorization action. We know that the MySQL username account consists of two parts, the user name and the hostname ([email protected]), where "host" can also use wildcard characters. If "%" means any character of any length, "_" means matching any single character. A. Deleting an anonymous user

1Mysql>SelectUser,host,password from user;2+-------------+-------------+-------------------------------------------+3| user | Host | password |4+-------------+-------------+-------------------------------------------+5| Root |                                           localhost | |6| Root |                                           Yinzhengjie | |7| Root |127.0.0.1| |8| |                                           localhost | |9| |                                           Yinzhengjie | |Ten| Yinzhengjie |Ten.%.%.%    | *c260a4f79fa905af65142ffe0b9a14fe0e1519cc | One+-------------+-------------+-------------------------------------------+ A 6RowsinchSet (0.00sec) -  -Mysql> theMysql> -Mysql> -Mysql> -mysql> Drop User""@localhost; +Query OK,0Rows Affected (0.01sec) -  +mysql> Drop User""@yinzhengjie; AQuery OK,0Rows Affected (0.00sec) at  -Mysql>SelectUser,host,password from user; -+-------------+-------------+-------------------------------------------+ -| user | Host | password | -+-------------+-------------+-------------------------------------------+ -| Root |                                           localhost | | in| Root |                                           Yinzhengjie | | -| Root |127.0.0.1| | to| Yinzhengjie |Ten.%.%.%    | *c260a4f79fa905af65142ffe0b9a14fe0e1519cc | ++-------------+-------------+-------------------------------------------+ - 4RowsinchSet (0.00sec) the  *Mysql>

B. Set the password for all root users

1 The first way:2MySQL > Set password for[email protected] = password ('Your_password');3 The second method of4MySQL > Update user set password = password ('Your_password') Where user ='Root';5MySQL >flush Peivileges;6 The Third Way:7[[Email protected] ~] #mysqladmin-u username-h Hot password'New_password'-P8[[Email protected] ~] #mysqladmin-u username-h host-p flush-privileges

four. Connect to MySQL server1.MySQL ProtocolThere are many kinds of MySQL clients, whether it is the kind of MySQL client connection mysqld Server, it is necessary to use the MySQL protocol protocol.  2.MySQL receive connection requestLocal communication:The client is located on the same host as the server side, and also communicates based on the 127.0.0.1 (localhost) address or the LO interface. Linux or Unix operating systems use UNIX to sock,/tmp/mysql.scok,/var/lib/mysql/mysql.sock these local files for interprocess communication. The widows is communicated through the memory (shared memory) pipe (named pipes). Remote communication:The client is on a different host than the server, or the unified host uses non-loopback address traffic. In the case of remote communication, it is usually based on socket communication, or TCP socket.  3.MySQL Client ToolsIn fact, there are many kinds of MySQL client tools, but not just MySQL (the most famous one when MySQL is the client). Other well-known client tools are: Mysqladmin,mysqldump,mysqlcheck and so on. options for client ([client]) communication:specified User:-u,--user=Specify host Name:-H,--host=Specify password:-P,--password=Specify protocol:--protocol={tcp|socket|memory|pipe}Specify port:--port= #MySQL默认是3306端口Specify socket:--socket= # For example:/tmp/mysql.sockmanagement tools for non-client classes: MYISAMCHK (monitoring tools) and Myisampack (packaging tools).  4.mysql command-line Optionscompressed transport:-compressCharacter Set:--default-character-setShow version number:-V:Show more information:-V:Specify CA certificate:--ssl-caCA Certificate folder:--ssl-capath (typically with multiple CA certificates, you can specify a directory)client Certificate:--ssl-certencryption Method:--ssl-cipherprivate key used:--ssl-keyVerify server-side certificate:--ssl-verify-server-certSpecify the database to use:--database=-Ddocuments with output of HTML format::-H--htmldocuments with output of XML format:-X--xml:reject uodate or delete command with no WHERE clause when sending command:--safe-updates 5.mysql Command Promptmysql>----> Wait for input command' >----> ' no end provided ', the next two lines are the same. ">' >----> Endurance prompt/*>----> Comment information (end on user "*/", e.g.:/*yinzhengjie*>) 6.mysql Command-line edit shortcut keysctrl+w: Delete A word before the cursor is locatedCtrl+u: Deletes everything before the cursor to the beginning of the line and saves it in the cache (buffer). ctrl+y: Paste the contents of ctrl+w or ctrl+u deleted beforeCtrl + A: Move the cursor to the beginningctrl+e: Move cursor to end of line Five. Mysqladmin Tool usageMysqladmin allows users to run SQL statements directly from the command line without having to go to the MySQL interactive interface. Command format: "mysqladmin [options] command [ARG] [Command[arg]] ..." and next, let's look at the usage of mysqladmin. This command is useful, especially when you're writing a shell script, and its usage can be great when you manipulate the data. 1. Create an empty database

1[[Email protected] ~]# mysqladmin Create Yinzhengjiedb #创建一个叫yinzhengjiedb的数据库. 2[Email protected] ~]# MYSQL-E'show databases;' #我们可以用mysql的-e option to verify whether the creation was successful,3+--------------------+4| Database |5+--------------------+6| Information_schema |7| MySQL |8| Test |9| Yinzhengjie |Ten| Yinzhengjiedb | One+--------------------+ A[Email protected] ~]#

2. Deleting a database

1[Email protected] ~]# MYSQL-E'show databases;'2+--------------------+3| Database |4+--------------------+5| Information_schema |6| MySQL |7| Test |8| Yinzhengjie |9| Yinzhengjiedb |Ten+--------------------+ One[[Email protected] ~]# mysqladmin Drop Yinzhengjiedb ADropping the database is potentially a very bad thing to Do. -Any data storedinchThe database would be destroyed. -  theDo your really want to drop the'Yinzhengjiedb'Database [y/N] y #此处我们需要交互式输入一个y.  -Database"Yinzhengjiedb"dropped -[Email protected] ~]# MYSQL-E'show databases;' -+--------------------+ +| Database | -+--------------------+ +| Information_schema | A| MySQL | at| Test | -| Yinzhengjie | -+--------------------+ -[Email protected] ~]#

MySQL initialization and use of client tools