How to create & manage mysql databases, tables & users

Создайте Учётную Запись Пользователя MySQL и Предоставьте Неограниченные Права Доступа

Как только вы начнёте использовать MySQL, вам будет предоставлено имя пользователя и пароль. Эти базовые учётные данные дают вам root-доступ, или полный контроль над всеми базами данных и таблицами.


Однако бывают случаи, когда вам необходимо дать доступ к базе данных кому-то другому, не предоставляя им полный контроль.

Например, вы нанимаете разработчиков для поддержки ваших баз данных, но не хотите, чтобы они могли удалять или изменять конфиденциальную информацию.

В таком случае просто предоставьте им учётные данные пользователя без полномочий root. Таким образом, определяя, что они могут и не могут делать с вашими данными.

Создать пользователя MySQL очень просто. В этой части мы объясним, как создать пользователя MySQL и назначить ему полные права доступа к базе данных. На практике было бы неразумно предоставлять полный контроль пользователю без полномочий root. Тем не менее это хорошая отправная точка для изучения пользовательских привилегий.

Чтобы создать новую учётную запись пользователя в MySQL, выполните следующие действия:

  1. Запустите командную строку и из неё сервер MySQL:
    mysql
  2. Сценарий вернёт следующий результат, подтверждающий, что вы обращаетесь к серверу MySQL.
    mysql>
  3. Затем выполните следующую команду:
    CREATE USER 'novyi_polzovatel'@'localhost' IDENTIFIED BY 'parol';
  4. novyi_polzovatel — это имя, которое мы дали нашей новой учётной записи, а часть IDENTIFIED BY ‘parol’ устанавливает пароль для этого пользователя. Вы можете заменить значения внутри кавычек.
  5. Чтобы назначить созданном пользователю неограниченные права доступа к базе данных, выполните следующую команду:
    GRANT ALL PRIVILEGES ON * . * TO 'novyi_polzovatel'@'localhost';
  6. Для того, чтоб изменения вступили в силу запустите команду обновления:
    FLUSH PRIVILEGES;

Это всё. Ваш новый пользователь получил те же права доступа в базе данных, что и пользователь root.

Возможные ошибки

1. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Причина: в новых версиях по умолчанию активированы политики на проверку сложности пароля. Их список можно посмотреть командой:

> SHOW VARIABLES LIKE ‘validate_password%’;

Вывод команды будет, примерно, следующим:

+—————————————+———+ | Variable_name | Value | +—————————————+———+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +—————————————+———+

* где:

  • validate_password_check_user_name — пароль не должен совпадать с именем пользователя.
  • validate_password_dictionary_file — использовать специальный файл со словарем запрещенных паролей.
  • validate_password_length — минимальная длина пароля.
  • validate_password_mixed_case_count — сколько, как минимум, должно быть символов в малой и большой раскладках.
  • validate_password_number_count — какое минимальное количество цифр использовать в пароле.
  • validate_password_policy — позволяет задать определенный набор правил. Доступны значения LOW (или 0), MEDIUM (1), STRONG (2).
  • validate_password_special_char_count — минимальное количество специальных символов (например, # или !).

Решение:

  1. Привести пароль в соответствие требованиям политик.
  2. Отключить политику, которая не позволяет использовать желаемый пароль. Например, чтобы отключить требование использовать цифры вводим:

> SET GLOBAL validate_password_number_count = 0;

2. ERROR 1728 (HY000): Cannot load from mysql.tables_priv. The table is probably corrupted

Причина: система считает, что таблица tables_priv в базе mysql неисправна.

Решение: чаще всего, ошибка возникает при переносе баз с одного сервера в другой. Если версии СУБД разные, таблица tables_priv может работать со сбоями. Для исправления необходимо выполнить команду mysql_upgrade — она проверяет все таблицы на совместимость с текущей версией MySQL/MariaDB и вносит исправления. Применение команды:

mysql_upgrade -u root -p

Как создать нового пользователя

В первой части руководства по MySQL мы вносили все изменения в настройки MySQL под root-пользователем, имея полный доступ ко всем базам данных. Однако для случаев, когда могут потребоваться более жесткие ограничения, есть способы создания пользователей с особыми наборами прав доступа.

Давайте начнем с создания нового пользователя из консоли MySQL:

К сожалению, на данном этапе пользователь “newuser” не имеет прав делать что-либо с базами данных. На самом деле, даже если если пользователь “newuser” попробует залогиниться (с паролем “password”), он не попадет в консоль MySQL.

Таким образом, первое, что нам необходимо сделать, это предоставить пользователю доступ к информации, которая ему потребуется.

Звездочки в этой команде задают базу и таблицу, соответственно, к которым у пользователя будет доступ. Конкретно эта команда позволяет пользователю читать, редактировать, выполнять любые действия над всеми базами данных и таблицами.

Поле завершения настройки прав доступа новых пользователей, убедитесь, что вы обновили все права доступа:

Теперь ваши изменения вступят в силу.

How to Add Information to a MySQL Table

We have a working table for our party. Now it’s time to start filling in the details.

Use this format to insert information into each row:

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');
 Query OK, 1 row affected (0.00 sec)
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Sandy", "Key Lime Tarts","N", '2012-04-14');
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tom", "BBQ","Y", '2012-04-18');
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tina", "Salad","Y", '2012-04-10'); 

We can take a look at our table:

mysql> SELECT * FROM potluck;
+----+-------+----------------+-----------+-------------+
| id | name  | food           | confirmed | signup_date |
+----+-------+----------------+-----------+-------------+
|  1 | John  | Casserole      | Y         | 2012-04-11  |
|  2 | Sandy | Key Lime Tarts | N         | 2012-04-14  |
|  3 | Tom   | BBQ            | Y         | 2012-04-18  |
|  4 | Tina  | Salad          | Y         | 2012-04-10  |
+----+-------+----------------+-----------+-------------+
4 rows in set (0.00 sec)

MySQL Create Database using Workbench

Before we start creating a database in Workbench, let me open the Workbench. From the below screenshot, you can see a list of the available database under the Schemas pane.

MySQL Create Database using Query in Workbench

In this example, we create a new database called second_database.

Click on the Execute button to execute the create database command

From the below screenshot, you can see the command executed successfully, and you can see the second_database under schemas

Create Database using Wizard in Workbench

If you are unaware or not familiar with queries, use Workbench to create a new MySQL database. To do so, within the Menu, click on the Create a New Schema in the Connected Server button.

Once you select the option, the following tab window will open. Please change the Name of the schema from new_schema to Third_Database

You can use the Collation drop-down box to change the Server Default collation.

Click the apply button to create a new schema or database.

TIP: We configured the MySQL to convert the schema names and table names to lowercase by default. Because we used the Third_Database as the schema, it is saying that it will convert the name to lowercase.

Click Apply button


Next, click the Finish button

Now, you can see the third_database under the schemas.

TIP: If you didn’t find the newly created database in Workbench, Please click on the refresh button beside the Schemas

Creating a new database using MySQL Workbench

To create a new database using the MySQL Workbench, you follow these steps:

First, launch the MySQL Workbench and click the setup new connection button as shown in the following screenshot:

Second, type the name for the connection and click the Test Connection button.

MySQL Workbench displays a dialog asking for the password of the user:

You need to (1) type the password for the user, (2) check the Save password in vault, and (3) click OK button.

Third, double-click the connection name Local to connect to the MySQL Server.

MySQL Workbench opens the following window which consists of four parts: Navigator, Query, Information, and Output.

Fourth, click the create a new schema in the connected server button from the toolbar:

In MySQL, the schema is the synonym for the database. Creating a new schema also means creating a new database.

Fifth, the following window is open. You need to (1) enter the schema name, (2) change the character set and collation if necessary, and click the Apply button:

Sixth, MySQL Workbench opens the following window that displays the SQL script which will be executed. Note that the statement command has the same effect as the statement.

If everything is fine, you will see the new database created and showed in the schemas tab of the Navigator section.

Seventh, to select the database, (1) right click the database name and (2) choose Set as Default Schema menu item:

The node is open as shown in the following screenshot.

Now, you can work with from the MySQL Workbench.

In this tutorial, you have learned how to create a new database from mysql program using the MySQL statement and from MySQL Workbench using the statement.

  • Was this tutorial helpful?

Creating Database Tables

To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

Example

Try out following example to create a table −

<?php
   
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   echo 'Connected successfully';
   
   $sql = 'CREATE TABLE employee( '.
      'emp_id INT NOT NULL AUTO_INCREMENT, '.
      'emp_name VARCHAR(20) NOT NULL, '.
      'emp_address  VARCHAR(20) NOT NULL, '.
      'emp_salary   INT NOT NULL, '.
      'join_date    timestamp(14) NOT NULL, '.
      'primary key ( emp_id ))';
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not create table: ' . mysql_error());
   }
   
   echo "Table employee created successfully\n";
   
   mysql_close($conn);
?>

In case you need to create many tables then its better to create a text file first and put all the SQL commands in that text file and then load that file into $sql variable and excute those commands.

Consider the following content in sql_query.txt file

CREATE TABLE employee(
   emp_id INT NOT NULL AUTO_INCREMENT,
   emp_name VARCHAR(20) NOT NULL,
   emp_address  VARCHAR(20) NOT NULL,
   emp_salary   INT NOT NULL,
   join_date    timestamp(14) NOT NULL,
   primary key ( emp_id ));
<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $query_file = 'sql_query.txt';
   
   $fp = fopen($query_file, 'r');
   $sql = fread($fp, filesize($query_file));
   fclose($fp); 
   
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not create table: ' . mysql_error());
   }
   
   echo "Table employee created successfully\n";
   mysql_close($conn);
?>

php_and_mysql.htm

Previous Page Print Page

Next Page  

Предоставление пользовательских привилегий

Здесь приведен короткий список распространенных прав, которые можно передать пользователю.

  • ALL PRIVILEGES: как было сказано раньше, такие привилегии разрешают пользователю MySQL полный доступ к указанным базам данных (если база данных не указана, такие привилегии распространяются на всю систему);
  • CREATE: позволяет пользователю создавать новые БД или таблицы;
  • DROP: позволяет удалять БД или таблицы;
  • DELETE: позволяет удалять строки таблиц;
  • INSERT: позволяет вносить строки в таблицы:
  • SELECT: позволяет использовать команду «Select» для чтения баз данных;
  • UPDATE: позволяет обновлять строки таблиц;
  • GRANT OPTION: позволяет передавать или ограничивать привилегии других пользователей.

Чтобы передать определенному пользователю определенные привилегии, используйте данный шаблон:

Чтобы открыть доступ ко всем базам данных или таблицам, нужно внести символ звездочки (*) вместо имени БД или таблицы.

Команду Flush Privileges необходимо использовать после каждого обновления или изменения привилегий.

Чтобы аннулировать привилегии, используйте шаблон, очень похожий на предыдущий:

Команда «DROP» может быть использована не только для удаления баз данных, но и для удаления всех пользователей:

Чтобы протестировать ново пользователя, выйдите из системы, набрав:

и войдите снова, введя в терминал команду:

MySQLVPS

How to Create a MySQL Table

Let’s imagine that we are planning a get together of friends. We can use MySQL to track the details of the event.

Let’s create a new MySQL table:

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1), 
signup_date DATE);

This command accomplishes a number of things:

  1. It has created a table called potluck within the directory, events.
  2. We have set up 5 columns in the table—id, name, food, confirmed, and signup date.
  3. The “id” column has a command (INT NOT NULL PRIMARY KEY AUTO_INCREMENT) that automatically numbers each row.
  4. The “name” column has been limited by the VARCHAR command to be under 20 characters long.
  5. The “food” column designates the food each person will bring. The VARCHAR limits text to be under 30 characters.
  6. The “confirmed” column records whether the person has RSVP’d with one letter, Y or N.
  7. The “date” column will show when they signed up for the event. MySQL requires that dates be written as yyyy-mm-dd

Let’s take a look at how the table appears within the database using the «SHOW TABLES;» command:

 mysql> SHOW TABLES;
+------------------+
| Tables_in_events |
+------------------+
| potluck          |
+------------------+
1 row in set (0.01 sec)

We can remind ourselves about the table’s organization with this command:

 DESCRIBE potluck;

Keep in mind throughout that, although the MySQL command line does not pay attention to cases, the table and database names are case sensitive: potluck is not the same as POTLUCK or Potluck.

 mysql>DESCRIBE potluck;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | YES  |     | NULL    |                |
| food        | varchar(30) | YES  |     | NULL    |                |
| confirmed   | char(1)     | YES  |     | NULL    |                |
| signup_date | date        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

Naming Conventions

It’s a good idea to keep a consistent naming convention. You could call the database (title case), (uppercase), (lowercase), (with an underscore separator), etc.

Some people also like to prefix their database objects. For example, prefixing tables with or and stored procedures with or . So a table could be called or etc.

You should also think about plurals. Do you call a table that holds all customer records or . If you choose , then you should also call a table that holds all products and not .

Whichever you choose, you should try to maintain the same convention throughout your database.

Establishing a naming convention will help enormously when you start to write queries against your database. You won’t have to remember whether you named a particular table with an underscore, title case, etc.

  • MySQL Workbench
  • Create a Table

What is MySQL?

  • MySQL is a database system used on the web
  • MySQL is a database system that runs on a server
  • MySQL is ideal for both small and large applications
  • MySQL is very fast, reliable, and easy to use
  • MySQL uses standard SQL
  • MySQL compiles on a number of platforms
  • MySQL is free to download and use
  • MySQL is developed, distributed, and supported by Oracle Corporation
  • MySQL is named after co-founder Monty Widenius’s daughter: My

The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.

Databases are useful for storing information categorically. A company may have a database with the following tables:

  • Employees
  • Products
  • Customers
  • Orders

Creating Databases

Before doing anything else with the data, you need to create a database. A database is a container of data. It stores contacts, vendors, customers or any kind of data that you can think of.

In MySQL, a database is a collection of objects that are used to store and manipulate data such as tables, database views, triggers, and stored procedures.

To create a database in MySQL, you use the  statement as follows:

Let’s examine the  statement in greater detail:

  • Followed by the   statement is database name that you want to create. It is recommended that the database name should be as meaningful and descriptive as possible.
  • The  is an optional clause of the statement. The clause prevents you from an error of creating a new database that already exists in the database server. You cannot have 2 databases with the same name in a MySQL database server.

For example, to create database, you can execute the  statement as follows:

After executing this statement, MySQL returns a message to notify whether the new database has been created successfully or not.

Using DROP DATABASE IF EXISTS

Using the above is great as long as you have no intention of replacing the database (and all its data) with a fresh new one. But sometimes you might want to delete the old database and start again from scratch. This is where comes in handy.

You can use to drop all tables in the database and delete the database before running the statement. You can combine that with to specify that the statement should only be executed if the database currently exists. This will prevent an error occuring if you try to drop a database that doesn’t actually exist.

So here’s what that could look like:

DROP DATABASE IF EXISTS FruitShop; CREATE DATABASE FruitShop;

Introduction to index

An index is a data structure such as B-Tree that improves the speed of data retrieval on a table at the cost of additional writes and storage to maintain it.

The query optimizer may use indexes to quickly locate data without having to scan every row in a table for a given query.

When you create a table with a primary key or unique key, MySQL automatically creates a special index named . This index is called the clustered index.

The index is special because the index itself is stored together with the data in the same table. The clustered index enforces the order of rows in the table.

Other indexes other than the index are called secondary indexes or non-clustered indexes.

The phone book analogy

Suppose you have a phone book that contains all the names and phone numbers of people in a city. Let’s say you want to find Bob Cat’s phone number. Knowing that the names are alphabetically ordered, you first look for the page where the last name is , then you look for and his phone number.

Now, if the names in the phone book were not sorted alphabetically, you would need to go through all pages, reading every name on it until you find . This is called sequential searching. You go over all the entries until you find the person with the phone number that you are looking for.

Relating the phone book to the database table, if you have the table and you have to find the phone number of , you would perform the following query:

It is pretty easy. Although the query is fast, the database has to scan all the rows of the table until it finds the row. If the table has millions of rows, without an index, the data retrieval would take a lot of time to return the result.

🚫 Расширение файла .mysql часто дается неправильно!

По данным Поиск на нашем сайте эти опечатки были наиболее распространенными в прошлом году:

msql

Мы нашли следующие аналогичные расширений файлов в нашей базе данных:

.msql W3-MSQL Script

Не удается открыть файл .mysql?

Если дважды щелкнуть файл, чтобы открыть его, Windows проверяет расширение имени файла. Если Windows распознает расширение имени файла, файл открывается в программе, которая связана с этим расширением имени файла. Когда Windows не распознает расширение имени файла, появляется следующее сообщение:

Windows не удается открыть этот файл: пример.mysql Чтобы открыть этот файл, Windows необходимо знать, какую программу вы хотите использовать для его открытия…

Если вы не знаете как настроить сопоставления файлов .mysql, проверьте FAQ.

Можно ли изменить расширение файлов?

Изменение имени файла расширение файла не является хорошей идеей. Когда вы меняете расширение файла, вы изменить способ программы на вашем компьютере чтения файла. Проблема заключается в том, что изменение расширения файла не изменяет формат файла.

Если у вас есть полезная информация о расширение файла .mysql, напишите нам!

Оцените нашу страницу MYSQL

Пожалуйста, помогите нам, оценив нашу страницу MYSQL в 5-звездочной рейтинговой системе ниже. (1 звезда плохая, 5 звезд отличная)

<< Расширение файла .mys

Расширение файла .myt >>

How to Check MySQL Database name exists or not

Let us see what will happen when we create a MySQL new database with an existing name. From the below screenshot, you can see it is throwing an error saying: Can’t create database third_database. Database exists.

NOTE: In an organization, we may or may not have the privileges to know the available databases. So, it is always advisable to check whether the database name already exists or not


If you have the privilege to see the available databases, then you can use the SHOW DATABASES command to see a list of databases. If not, use the below option.

The following statement will only execute the MySQL Create Database Statement if the third_database is not available in the system database

Let me try a different database name in the Create Database command

Database Design

Before you create your database you need to think about its design. You need to think about what tables and other objects it will contain, as well as the data that will be stored in the database.

In reality, when you create a database, you don’t just create an empty database and then think about what tables will go into it later. You would normally write a script that creates the database and all its tables and other objects all in one go.

Fortunately, doing this is quite straightforward. It is simply a matter of combining SQL statements, one after the other, for each object you want to create.

We’ll look at creating tables next, but for now, here’s a quick example:

DROP DATABASE IF EXISTS FruitShop; CREATE DATABASE FruitShop; USE FruitShop;

CREATE TABLE Fruit (FruitName VARCHAR(20), DateEntered DATETIME);

This is a simple example that creates a database and a table within that database. You could write a long script that creates a database and many tables as well as other database objects.

You could also write another script that populates the tables with the initial data required. You could even combine both scripts into one if you wish.

How to Access the MySQL shell

Once you have MySQL installed on your droplet, you can access the MySQL shell by typing the following command into terminal:

mysql -u root -p

After entering the root MySQL password into the prompt (not to be confused with the root droplet password), you will be able to start building your MySQL database.

Two points to keep in mind:

  • All MySQL commands end with a semicolon; if the phrase does not end with a semicolon, the command will not execute.
  • Also, although it is not required, MySQL commands are usually written in uppercase and databases, tables, usernames, or text are in lowercase to make them easier to distinguish. However, the MySQL command line is not case sensitive.

MySQL workbench ER diagram forward engineering

MySQL workbench has utilities that support forward engineering. Forward engineering is a technical term is to describe the process of translating a logical model into a physical implement automatically.

We created an ER diagram on our ER modeling tutorial. We will now use that ER model to generate the SQL scripts that will create our database.

Creating the MyFlix database from the MyFlix ER model

1.       Open the ER model of MyFlix database that you created in earlier tutorial.

2.       Click on the database menu. Select forward engineer

3.       The next window,  allows you to connect to an instance of MySQL server. Click on the stored connection drop down list and select local host. Click Execute

4.       Select the options shown below in the wizard that appears. Click next

5.       The next screen shows the summary of objects in our EER diagram. Our MyFlix DB has 5 tables.  Keep the selections default and click Next.

6..       The window shown below appears. This window allows you to preview the SQL script to create our database. We can save the scripts to a *.sql» file or copy the scripts to the clipboard. Click on next button

7. The window shown below appears after successfully creating the database on the selected MySQL server instance.

The CREATE DATABASE Statement

You can create a new database using the statement. This statement is part of SQL, which is a special-purpose language for querying and programming databases.

The syntax is where is the name of the database you want to create.

For example, to create a database called type the following into the Query Tab and run the query:

CREATE DATABASE FruitShop;

That’s all it takes. The SCHEMAS tab in the left menu should now be displaying your database:

If you can’t see your database, click the little refresh icon next to the SCHEMAS heading.

You can also use the following command to display a list of databases on the server:

SHOW DATABASES;

But of course, you now have an empty database. You will need to add tables and insert data before you have a fully functioning database. We’ll get to that soon.


С этим читают