MYSQL > Install MYSQL from scratch

Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities

preface

This column can also be considered study notes, because the previous column is on the network training institutions to learn tutorial, completed although some found some in-depth study, but were not particularly deep, so from the start of the section will start from scratch to fully understand the MYSQL, here looking for a book to understand the MYSQL on the root, I also strongly recommend readers to have a look at this book, which not only has new features to explain, but also has a lot of dry goods. At the same time, it is also very good. As a support, I also bought a physical book (although most of them are read in PDF).

Mind maps (constantly updated)

www.mubucm.com/doc/7DDOY0C…

Photo address: p3-juejin.byteimg.com/tos-cn-i-k3…

References:

  1. English mysql5.7 official documentation: dev.mysql.com/doc/refman/…
  2. Corresponding translation in Chinese website (machine) : www.docs4dev.com/docs/zh/mys…

An overview of the

  1. How to understand the client and server of mysql
  2. Learn about the DOS and don ‘ts of installing mysql and review mysql personally
  3. This section describes four common commands used to start mysql and their functions
    1. mysqld
    2. mysqld_safe
    3. mysql.server
    4. mysqld_multi

Get to know clients and servers

Mysql is divided into client side and server side. Usually when we visit a page or browse data, it is a database access process (of course, most things are static now). So the side that connects is called the client and the side that receives the request is called the server.

Basic tasks of mysql

In general, we use MYSQL to basically do these things:

  1. Connect to the database.
  2. Query the database data, the client sends a request to the server, the server finds the data according to the command and sends it back to the client.
  3. Disconnect from the database.

Mysql instance

With that said, let’s talk about mysql instances. Instances are also called processes at the operating system level, and processes can be thought of as abstractions of processors, memory, and IO devices. We don’t need to know how this process actually transfers and stores data, we just need to know that it needs a port, And each instance has a process ID, when the database instance to run the system will assign a process ID and ensure that the only to it, and each process has its own name, the name is installed by the programmer set himself, but if there is no allocation MYSQL themselves the name of the default Settings will be used.

The default name of the MySQL server process we started is mysqld, while the default name of our usual MySQL client process is MySQL.

From this name we can also guess why we usually use Mysqld to start a service and mysql to connect to a database.

  • Each file is an abstraction of the IO device
  • Virtual memory is an abstraction of memory and IO devices
  • Processes are abstractions of processors, virtual memory, and IO devices

Precautions for installing Mysql

Mysql installation is very simple, but in fact if the full manual installation details are more, usually we use direct EXE program or direct use of BIN package, but many times for Linux software many people will recommend the use of source installation, source installation benefits are not only reduced size, After a lot of experiments, it is proved that the installation efficiency of source code will be improved, so it is possible to use source code installation in the formal environment. Finally, it is necessary to pay attention to the following points: RPM packages used in Linux will have separate server and client RPM packages, which need to be installed separately.

The installation directory location is different

The following is the specific Mysql installation directory, of course, the following is only for reference, personal MAC computer is using Brew install Mysql plus M1 chip installation, compatibility is unknown, so in order to ensure the reliability of the notes, here uses the Windows system to carry out the actual test and drill. Different operating systems may have slightly different locations in the mysql installation directory, but it is important to be aware of the actual installation location of mysql so that you can fiddle with various commands and set parameters.

Installation directory on macOS: /usr/local/mysql.Installation directory on Windows: C:\Program Files\ mysql \ mysql Server5.7
Copy the code

Mysql installation

Windows Installation Process

If you use mysql-installxx.exe to install mysql, you will find the following command:

'mysql' is not an internal or external command, nor is it a runnable programCopy the code

Your first reaction to this prompt is to go into Power Shell’s administrator mode:

PS C:\Windows\system32> mysql -uroot -p mysql: cannot identify 'mysql' as cmdlet, function, script file or name of executable program. Check the spelling of the name, and if the path is included, make sure the path is correct, then try again. 1 + mysql -uroot -p + ~~~~~ + CategoryInfo: ObjectNotFound: (mysql:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundExceptionCopy the code

Mysql has been started again. This is a bit strange

Mysql > install mysql by using power shell; mysql > install mysql by using power shell;

PS C:\Windows\system32> mysql -uroot -pxxxxxx mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.35-log MySQL Community Server (GPL) Copyright (C) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql>Copy the code

About absolute and relative path startup issues:

Absolute path: If you do not have access to your application commands in the system environment variable, you need to go to the relevant directory to execute the command. For example, you need to go to the C:\Program Files\MySQL\MySQL Server 5.7bin directory to execute the command. You can also use mysql normally, but it’s a hassle every time you do that, so basically a normal person will use environment variables, and if you don’t know what environment variables are, uh… Please search by yourself

Relative path: After setting environment variables, we will find our commands and execute them according to the order of setting environment variables. However, this is a bit special in mysql. As will be explained later, if multiple system parameters are configured, the last configuration parameter will be used by default.

​

Macos installation process

Mac book personal is 24 installments only dare to touch the sacred things, I believe that the use of people is not much, so here directly put a post:

www.cnblogs.com/nickchen121…

Linux Installation Process

As I use cloud server to build mysql for many times, here provides an aliyun RPM package installation method, version is Centeros7. Centeros6 can also be used, but some commands need to be modified.

Juejin. Cn/post / 689525…

To start the Mysql:

Mysqld_muti = mysqLD_muti; mysqLD_muti = mysqLD_muti; mysqLD_muti = mysqLD_muti;

mysqld

Mysqld: represents the mysql server program, which can be run to start a server process, but is not commonly used.

After using the mysqld command in practice, I found the following results. At first, there was a puzzling problem, but it was not difficult to find the problem by looking at the log. In fact, the directory did not exist and mysql could not create a directory for you. Just use everything to find the corresponding file (why doesn’t the MAC have such a good software, ouch).

You can do this by going to the my.ini file below and changing the path to datadir.

Open this file to find /Data in the configuration path, and then find that the path is not in the directory:

# the Path to the database root datadir = C: / ProgramData/MySQL/MySQL Server 5.7 / DataCopy the code

Here are the results of running the log described above, if you are interested, try it out for yourself, or you may not encounter my problem

PS C:\Windows\system32> mysqld -datadir=D:\soft\mysqltest mysqld: Can't change dir to 'C:\Program Files\MySQL\MySQL Server 5.7\data\' (Errcode: 2 - No such file or directory) 2021-10-28T14:20:14.063607z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-10-28T14:20:14.063669Z 0 [Note] -- Secure-file-priv is set to NULL. Operations related to Importing and exporting Data are disabled 2021-10-28T14:20:14.064027z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe (mysqld Starting as Process 3296... 2021-10-28T14:20:14.066088z 0 [Warning] Can't create test file C: Program Files\MySQL\MySQL Server 5.7\data\DESKTOP- l8ad9hm.lower -test 2021-10-28t14:20:14.066416z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\DESKTOP- l8ad9hm. lower-test 2021-10-28t14:20:14.067090z 0 [ERROR] Failed to set datadir To C: Program Files\MySQL\MySQL Server 5.7\data\ 2021-10-28t14:20:14.067408z 0 [ERROR] Aborting 2021-10-28T14:20:14.067619z 0 [Note] Binlog End 2021-10-28T14:20:14.067904z 0 [Note] C: Program Files MySQL Server 5.7 \ bin \ mysqld. Exe: Shutdown is completeCopy the code

Finally you can run mysqld to start a server process and build the corresponding files in the corresponding directory.

The answer is inspired by the following section:

What I did (Windows 10) for a new installation:

  1. Start cmd in admin mode (run as administrator by hitting windows key, typing cmd, right clicking on it and selecting “Run as Administrator”

  2. Change into “MySQL Server X.Y” directory (for me the full path is C: Program Files MySQL Server 5.7)

  3. using notepad create a my.ini with a mysqld section that points at your data directory

    [mysqld]
datadir="X:\Your Directory Path and Name"
Copy the code

  4. created the directory identified in my.ini above.

  5. change into bin Directory under server directory and execute: mysqld --initialize

  6. Once complete, started the service and it came up fine.

mysqld_safe

Mysqld_safe is a startup script that uses mysqLD as an example to create an error log. If you use mysqLD_safe to start a server, you need to redirect the error information to a file that contains the error information.

But can’t find after the individual practice, actually the reason was that the Windows do not have this command, about more mysqld_safe command explanation can see mysql’s official website: dev.mysql.com/doc/refman/…

If you have difficulty in reading English, there is a Chinese translation website: www.docs4dev.com/docs/zh/mys…

For some Linux platforms, MySQL installed using RPM or Debian packages includes system support for ManagementMySQL server startup and shutdown. On these platforms it may not be considered necessary to have all mysqL. server and mysqLD_safe installed.

mysql.server

This file is also a startup script, which is the most commonly used script. In fact, this command can be thought of as a link, i.e. a “shortcut”, which actually points to the path:.. /support-files/mysql.server /mysql.server/mysqld_safe /mysql.server /support-files/mysql.server

mysql.server start
Copy the code

If the operating system does not create the corresponding link file after installation, you may need to manually create a link file. In addition, the Linux server needs to pay attention to the permission problem, because sometimes the root permission does not exist, you may need to configure the corresponding directory user group

If you install mysql from a source distribution or in a binary distribution format that does not automatically install mysql.server, you can install scripts manually. It can be found in support-files in the MySQL installation directory or in the MySQL source tree. Copy the script to /etc/init.d directory named mysql and make it executable:

shell> cp mysql.server /etc/init.d/mysql
shell> chmod +x /etc/init.d/mysql
Copy the code

Finally, mysql can be started and shut down using the following methods (Linux) :

mysql.server start
mysql.server stop
Copy the code

On Windows, the above command will cause an error, so we can use the following command:

PS C:\Windows\system32> mysql.server startMysql. server: The mysql.server entry cannot be recognized as the name of a cmdlet, function, script file, or runnable program. Check the spelling of the name, and if paths are included, make sure the path is correct, then try again. Location line:1Character:1
+ mysql.server start
+ ~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (mysql.server:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

PS C:\Windows\system32> net startThe mysql service name is invalid. Please type NET HELPMSG2185To get more help. PS C:\Windows\system32> netstartThe service requested by mysql57 has been started.Copy the code

mysqld_multi

There are times when you might want to use multiple server processes on a single machine. The purpose of this command is to start and stop each server process, but because of the complexity of this command, I recommend using the official stable link above for details.

If you have difficulty in reading English, there is a Chinese translation website: www.docs4dev.com/docs/zh/mys…

The Window & service starts

Mysql on Windows is basically for the sake of taking care of Windows users. The real place to play is Linux. Of course, some companies do use Windows as a server. So to pass, here is the command to install a Windows service:

"Complete executable file path" --install [-manual]Copy the code

The -manual can be omitted. The difference is that the automatic startup is turned off and replaced with manual startup

Finally, the following is a personal mysqld service installation command, please install according to your own system environment.

C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld --install
Copy the code

Net start mysql and net stop mysql can be used to start or stop mysql.

Mysql connection

There is only one point to note here. For command formats, use a dash when using -u and -p, but use a double dash when using –username and –password.

conclusion

This section is very simple, introduces the mysql installation process of stomp pits and four common startup commands, in fact, we only need to master one command, at the same time for part of the command is more recommended to use while learning while recording can better digest and absorb.

The above is the record of the author learning while stepping on the pit, and finally found that the best tutorial is still the official document, in addition, do not panic when you encounter problems, first boldly guess the problem point in your mind, verify and retry repeatedly, stepping on the pit after more natural will be familiar with.

Write in the last

This article is a new compilation of the column, and the follow-up will make a review and summary of the previous learning content.