Ubuntu 20.04 installing MySQL and configuring MySQL workbench

preface

Recently, when learning Lin Ziyu's big data, I found that installing Hive for Hadoop requires using MySQL database to save Hive metadata, rather than using Hive's own derby to store metadata. In order to fill the gap in the big data column, consult the materials to complete this blog.

  • System version: Ubuntu 20.04

1, MySQL installation and configuration

1. Install MySQL

Install using the default apt package manager:

sudo apt-get update
sudo apt-get install mysql-server

Start MySQL:

service mysql start

Check for successful startup:

systemctl status mysql.service

Normal startup output is as follows:

Just observe its Active options.

2. Configure MySQL

Edit MySQL configuration file:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following row of data under mysqld:

character_set_server=utf8

As follows:

Restart your MySQL service:

service mysql restart

2, Configure MySQL remote access

Under Ubuntu, MySQL is only allowed to be accessed locally by default, and cannot be connected using MySQL workbench connection tool; So we need to create a new database and user to use MySQL workbench.

Enter the mysql shell interface:

sudo mysql -u root -p

Modify your password strength requirements and password length requirements:

mysql> set global validate_password.policy=LOW;
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password.length=8;
Query OK, 0 rows affected (0.00 sec)

mysql>

Note that if the MySQL 8. Version is lower, modify the above variables and use validate_password_policy . Use the following command to view your MySQL version:

mysql> SELECT @@VERSION;
+-------------------------+
| @@VERSION               |
+-------------------------+
| 8.0.27-0ubuntu0.20.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> 

New database and user:

mysql> CREATE DATABASE dbfzq;
Query OK, 1 row affected (0.02 sec)

mysql> create user 'fzq'@'%' identified by  'qwer1234';
Query OK, 0 rows affected (0.03 sec)

mysql> grant all privileges on dbfzq.* to 'fzq'@'%' with grant option;
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql>

dbfzq in line 1 is the name of the new database I created, fzq in line 4 is the new user name I created, and qwer1234 is the password (self configured according to the length and strength of the password previously set)

3, Installing and configuring MySQL workbench

1. Install MySQL workbench

The download address of the installation package is as follows: https://dev.mysql.com/downloads/workbench/

The package I downloaded here is MySQL workbench community_ 8.0.27-1ubuntu20.04_ Amd64.deb, and then install the downloaded DEB package:

sudo dpkg -i ~/Downloads/mysql-workbench-community_8.0.27-1ubuntu20.04_amd64.deb

There may be dependency errors in the middle. Use the following command to install its dependencies:

sudo apt -f install

When the installation is complete, the software center finds and opens it.

2. Connect to the database using MySQL workbench

After opening, the interface is as follows:

Click the plus sign after MySQL Connections and try to connect the database (dbfzq) and user (fzq) created before:


The interface is as follows:


Try this:

create table person (id int(3) auto_increment not null primary key, xm varchar(10),xb varchar(2),csny date);
describe person;

Use it normally and you're done!

summary

Tags: MySQL Apache Ubuntu

Posted on Sat, 20 Nov 2021 03:50:43 -0500 by andybrooke