How to start mysql server linux

How to start mysql server linux

Last updated on: 2019-12-20

Authored by: Jered Heeschen

MySQL is an open-source relational database that is free and widely used. It is a good choice if you know that you need a database but don’t know much about all the available options.

This article describes a basic installation of a MySQL database server on the Ubuntu operating system. You might need to install other packages to let applications use MySQL, like extensions for PHP. Check your application documentation for details.

Install MySQL

Install the MySQL server by using the Ubuntu operating system package manager:

The installer installs MySQL and all dependencies.

If the secure installation utility does not launch automatically after the installation completes, enter the following command:

This utility prompts you to define the mysql root password and other security-related options, including removing remote access to the root user and setting the root password.

Allow remote access

If you have iptables enabled and want to connect to the MySQL database from another machine, you must open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application that uses MySQL is running on the same server.

Run the following command to allow remote access to the mysql server:

Start the MySQL service

After the installation is complete, you can start the database service by running the following command. If the service is already started, a message informs you that the service is already running:

Launch at reboot

To ensure that the database server launches after a reboot, run the following command:

Configure interfaces

MySQL, by default is no longer bound to ( listening on ) any remotely accessible interfaces. Edit the “bind-address” directive in /etc/mysql/mysql.conf.d/mysqld.cnf:

Restart the mysql service.

Start the mysql shell

There is more than one way to work with a MySQL server, but this article focuses on the most basic and compatible approach, the mysql shell.

At the command prompt, run the following command to launch the mysql shell and enter it as the root user:

When you’re prompted for a password, enter the one that you set at installation time, or if you haven’t set one, press Enter to submit no password.

The following mysql shell prompt should appear:

Set the root password

If you logged in by entering a blank password, or if you want to change the root password that you set, you can create or change the password.

For versions earlier than MySQL 5.7, enter the following command in the mysql shell, replace password with your new password:

For version MySQL 5.7 and later, enter the following command in the mysql shell, replacing password with your new password:

To make the change take effect, reload the stored user information with the following command:

Note: We’re using all-caps for SQL commands. If you type those commands in lowercase, they’ll work. By convention, the commands are written in all-caps to make them stand out from field names and other data that’s being manipulated.

If you need to reset the root password later, see Reset a MySQL root password.

View users

MySQL stores the user information in its own database. The name of the database is mysql. Inside that database the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:

The following list describes the parts of that command:

• SELECT tells MySQL that you are asking for data.
• User, Host, authentication_string tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case, you are looking for the username, the host associated with the username, and the encrypted password entry.
• FROM mysql.user » tells MySQL to get the data from the mysql database and the user table.
• A semicolon (;) ends the command.

Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.

User hosts

The following example is the output for the preceding query:

Users are associated with a host, specifically, the host from which they connect. The root user in this example is defined for localhost, for the IP address of localhost, and the hostname of the server. You usually need to set a user for only one host, the one from which you typically connect.

If you’re running your application on the same computer as the MySQL server, the host that it connects to by default is localhost. Any new users that you create must have localhost in their host field.

If your application connects remotely, the host entry that MySQL looks for is the IP address or DNS hostname of the remote computer (the one from which the client is coming).

Anonymous users

In the example output, one entry has a host value but no username or password. That’s an anonymous user. When a client connects with no username specified, it’s trying to connect as an anonymous user.

You usually don’t want any anonymous users, but some MySQL installations include one by default. If you see one, you should either delete the user (refer to the username with empty quotes, like ‘ ‘) or set a password for it.

Create a database

There is a difference between a database server and a database, even though those terms are often used interchangeably. MySQL is a database server, meaning it tracks databases and controls access to them. The database stores the data, and it is the database that applications are trying to access when they interact with MySQL.

Some applications create a database as part of their setup process, but others require you to create a database yourself and tell the application about it.

To create a database, log in to the mysql shell and run the following command, replacing demodb with the name of the database that you want to create:

After the database is created, you can verify its creation by running a query to list all databases. The following example shows the query and example output:

Add a database user

When applications connect to the database using the root user, they usually have more privileges than they need. You can add users that applications can use to connect to the new database. In the following example, a user named demouser is created.

To create a new user, run the following command in the mysql shell:

When you make changes to the user table in the mysql database, tell MySQL to read the changes by flushing the privileges, as follows:

Verify that the user was created by running a SELECT query again:

Grant database user permissions

Right after you create a new user, it has no privileges. The user can log in, but can’t be used to make any database changes.

Give the user full permissions for your new database by running the following command:

Flush the privileges to make the change official by running the following command:

To verify that those privileges are set, run the following command:

MySQL returns the commands needed to reproduce that user’s permissions if you were to rebuild the server. USAGE on \*.\* means the users gets no privileges on anything by default. That command is overridden by the second command, which is the grant you ran for the new database.

Summary

If you’re just creating a database and a user, you are done. The concepts covered here should give you a solid start from which to learn more.

Related articles

Share this information:

©2020 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License

Источник

How to start mysql server linux

This section describes how start the server on Unix and Unix-like systems. (For Windows, see Section 2.3.4.5, “Starting the Server for the First Time”.) For some suggested commands that you can use to test whether the server is accessible and working properly, see Section 2.10.3, “Testing the Server”.

Start the MySQL server like this if your installation includes mysqld_safe :

For Linux systems on which MySQL is installed using RPM packages, server startup and shutdown is managed using systemd rather than mysqld_safe , and mysqld_safe is not installed. See Section 2.5.9, “Managing MySQL Server with systemd”.

Start the server like this if your installation includes systemd support:

Substitute the appropriate service name if it differs from mysqld (for example, mysql on SLES systems).

It is important that the MySQL server be run using an unprivileged (non- root ) login account. To ensure this, run mysqld_safe as root and include the —user option as shown. Otherwise, you should execute the program while logged in as mysql , in which case you can omit the —user option from the command.

For further instructions for running MySQL as an unprivileged user, see Section 6.1.5, “How to Run MySQL as a Normal User”.

If the command fails immediately and prints mysqld ended , look for information in the error log (which by default is the host_name .err file in the data directory).

If the server is unable to access the data directory it starts or read the grant tables in the mysql schema, it writes a message to its error log. Such problems can occur if you neglected to create the grant tables by initializing the data directory before proceeding to this step, or if you ran the command that initializes the data directory without the —user option. Remove the data directory and run the command with the —user option.

Источник

Getting Started with MySQL

MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions to help you get MySQL up and running in a few easy steps. We also explain how to perform some basic operations with MySQL using the mysql client.

The following instructions do not necessarily apply to versions of MySQL older than MySQL 5.7.

These are instructions for a fresh installation only. If you are already using MySQL and want to upgrade to a newer version, see Upgrading MySQL.

For legal information, see the Legal Notices.

For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with other MySQL users.

Document generated on: 2021-09-23 (revision: 70881)

Table of Contents

Installing and Starting MySQL

There are different ways to install MySQL. The following covers the easiest methods for installing and starting MySQL on different platforms.

Linux. The easiest way to install MySQL is to use the MySQL repositories:

For Yum-based Linux distributions like Oracle Linux, Red Hat Enterprise Linux, and Fedora, follow the instructions in A Quick Guide to Using the MySQL Yum Repository. If your system cannot use the MySQL Yum repository for some reason, follow the instructions in Installing MySQL on Linux Using RPM Packages from Oracle.

For APT-based distributions like Debian and Ubuntu, follow the instructions in A Quick Guide to Using the MySQL APT Repository. If your system cannot use the MySQL APT repository for some reason, follow the instructions in Installing MySQL on Linux Using Debian Packages from Oracle.

For SUSE Linux Enterprise, follow the instructions in A Quick Guide to Using the MySQL SLES Repository. If your system cannot use the MySQL SUSE repository for some reason, follow the instructions in Installing MySQL on Linux Using RPM Packages from Oracle.

For Linux distributions that do not support the MySQL repositories or the installation packages mentioned above, you can install MySQL using generic binaries:

Download the .tar or .tar.gz archive for the generic binaries for Linux from the Download MySQL Community Server page.

See Installing MySQL on Unix/Linux Using Generic Binaries for instructions on installing the binaries.

After installing the binaries, following the instructions given in Initializing the Data Directory. It is especially important to note the random root password generated for you during the initialization process; see Initializing the Data Directory for more detail.

Next, follow the instructions given in Starting the Server.

Detailed instructions, as well as other methods for installation, can be found in Installing MySQL on Linux.

Microsoft Windows. The recommended way to install MySQL on Microsoft Windows is to use the MySQL Installer; see MySQL Installer Method on how to download and run the MySQL Installer. For a detailed explanation for each step of the installation wizard, see MySQL Installer for Windows.

If you have chosen to configure MySQL as a Windows service during the installation process, which is the default option (see Windows Service for details), the MySQL server will start automatically after the installation process is completed.

Detailed information regarding Windows installation, including alternative installation methods and instructions for troubleshooting, can be found in Installing MySQL on Microsoft Windows.

macOS. The recommended way for installing MySQL on macOS is to use the macOS installer package. See Installing MySQL on macOS Using Native Packages on how to download and run the installer package, and how to start the MySQL server afterward.

Detailed information regarding installation on macOS can be found in Installing MySQL on macOS.

Other platforms. For installations on other platforms (for example, FreeBSD and Solaris), as well as installation methods not covered above, see Installing and Upgrading MySQL.

Connecting to the MySQL Server with the mysql Client

Once your MySQL server is up and running, you can connect to it as the superuser root with the mysql client.

On Linux, enter the following command at the command line terminal (for installation using generic binaries, you might need to go first to the bin folder under the base directory of your MySQL installation):

On Windows, click Start , All Programs , MySQL , MySQL 5.7 Command Line Client (or MySQL 8.0 Command Line Client , respectively). If you did not install MySQL with the MySQL Installer, open a command prompt, go to the bin folder under the base directory of your MySQL installation, and issue the following command:

You are then asked for the root password, which was assigned in different manners according to the way you installed MySQL. The installation and initialization instructions given above already explain the root password, but here is a quick summary:

For installations using the MySQL Yum repository, MySQL SUSE repository, or RPM packages directly downloaded from Oracle, the generated root password is in the error log. View it with, for example, the following command:

For installations using the MySQL APT repository or Debian packages directly downloaded from Oracle, you should have already assigned the root password yourself; if you have not done that for some reason, see the «Important» note here or How to Reset the Root Password.

For installations on Linux using the generic binaries followed by a data directory initialization using mysqld —initialize as discussed in Initializing the Data Directory, the generated root password is displayed in the standard error stream during the data directory’s initialization:

Depending on the configuration you used to initialize the MySQL server, the error output might have been directed to the MySQL error log; go there and check for the password if you do not see the above message on your screen. The error log is a file with a .err extension, usually found under the server’s data directory (the location of which depends on the server’s configuration, but is likely to be the data folder under the base directory of your MySQL installation, or the /var/lib/mysql folder).

If you have initialized the data directory with mysqld —initialize-insecure instead, the root password is empty.

For installations on Windows using the MySQL Installer and OS X using the installer package, you should have assigned a root password yourself.

If you have forgotten the root password you have chosen or have problems finding the temporary root password generated for you, see How to Reset the Root Password.

Once you are connected to the MySQL server, a welcome message is displayed and the mysql> prompt appears, which looks like this:

At this point, if you have logged in using a temporary root password that was generated during the installation or initialization process (which will be the case if you installed MySQL using the MySQL Yum repository, or using RPM packages or generic binaries from Oracle), change your root password by typing the following statement at the prompt:

Until you change your root password, you will not be able to exercise any of the superuser privileges, even if you are logged in as root .

Here are a few useful things to remember when using the mysql client:

Client commands (for example, help , quit , and clear ) and keywords in SQL statements (for example, SELECT, CREATE TABLE, and INSERT) are not case-sensitive.

Column names are case-sensitive. Table names are case-sensitive on most Unix-like platforms, but not case-sensitive on Windows platforms. Case-sensitivity during string comparison depends on the character collation you use. In general, it is a good idea to treat all identifiers (database names, table names, column names, etc.) and strings as case-sensitive. See Identifier Case Sensitivity and Case Sensitivity in String Searches for details.

You can type your SQL statements on multiple lines by pressing Enter in the middle of it. Typing a semicolon ( ; ) followed by an Enter ends an SQL statement and sends it to the server for execution; the same happens when a statement is ended with \g or \G (with the latter, returned results are displayed vertically). However, client commands (for example, help , quit , and clear ) do not require a terminator.

To disconnect from the MySQL server, type QUIT or \q at the client:

Some Basic Operations with MySQL

Here are some basic operations with the MySQL server. SQL Statements explains in detail the rich syntax and functionality of the SQL statements that are illustrated below.

Showing existing databases. Use a SHOW DATABASES statement:

Creating a new database. Use a CREATE DATABASE statement:

Check if the database has been created:

Creating a table inside a database. First, pick the database in which you want to create the table with a USE statement:

The USE statement tells MySQL to use pets as the default database for subsequent statements. Next, create a table with a CREATE TABLE statement:

Data types you can use in each column are explained in Data Types. Primary Key Optimization explains the concept of a primary key. What follows a # on each line is a comment, which is ignored by the mysql client; see Comments for other comment styles.

Check if the table has been created with a SHOW TABLES statement:

DESCRIBE shows information on all columns of a table:

Adding records into a table. Use, for example, an INSERT. VALUES statement:

See Literal Values for how to write string, date, and other kinds of literals in MySQL.

Retrieving records from a table. Use a SELECT statement, and “ * ” to match all columns:

To select specific columns and rows by a certain condition using the WHERE clause:

Deleting a record from a table. Use a DELETE statement to delete a record from a table, specifying the criterion for deletion with the WHERE clause:

Adding or deleting a column from a table. Use an ALTER TABLE. ADD statement to add a column. You can use, for example, an AFTER clause to specify the location of the new column:

Use DESCRIBE to check the result:

SHOW CREATE TABLE shows a CREATE TABLE statement, which provides even more details on the table:

Use ALTER TABLE. DROP to delete a column:

See the Tutorial for more instructions on how to work with the MySQL server.

Other Important Tasks to Perform

Create more user accounts. root is a superuser account for administration of the MySQL server which should not be used for general operations. On how to create user accounts of various kinds, see Adding Accounts, Assigning Privileges, and Dropping Accounts.

Configure MySQL to be managed with systemd. If you have installed MySQL on a systemd platform using generic binaries and want it to be managed with systemd, see Managing MySQL Server with systemd.

Troubleshooting

The following are resources for troubleshooting some problems you might run into:

Other Helpful Resources

MySQL Reference Manual (select the version with the version switch)

Tutorial on using the mysql client program to work with MySQL

Legal Notices

Copyright © 1997, 2021, Oracle and/or its affiliates.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs) and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government end users are «commercial computer software» or «commercial computer software documentation» pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the license contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloud services are defined by the applicable contract for such services. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc, and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms:

You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above.

Источник