ติดตั้ง MySQL บน Ubuntu 20.04

ติดตั้ง MySQL

sudo apt update
sudo apt install mysql-server

ตรวจสอบสถานะ

systemctl status mysql

ลองใช้งาน

$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2023, 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>

สร้าง user

CREATE USER 'jack'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'jack'@'localhost';
FLUSH PRIVILEGES;
SELECT user,authentication_string,plugin,host FROM mysql.user;

ทดสอบที่ local จะเข้าได้ละ

$ sudo mysql -u jack  -p

เข้าจากเครื่องอื่น

1 Access mysqld.cnf File

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

2 Change Bind-Address IP

You now have access to the MySQL server configuration file. Scroll down to the bind-address line and change the IP address. The current default IP is set to 127.0.0.1. This IP limits MySQL connections to the local machine.

The new IP should match the address of the machine that needs to access the MySQL server remotely. For example, if you bind MySQL to 0.0.0.0, then any machine that reaches the MySQL server can also connect with it.

bind-address            = 0.0.0.0
sudo systemctl restart mysql

สร้าง remote user โดย remote ไปจากเครื่อง 192.168.1.124

CREATE USER 'jack'@'192.168.1.124' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'jack'@'192.168.1.124';
FLUSH PRIVILEGES;

Python3 ติดต่อดาต้าเบส MySQL

Create Connection

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword"
)

print(mydb)
# <mysql.connector.connection_cext.CMySQLConnection object at 0x000002A56602E948>

หรือระบุ database

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
)

Creating a Database

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

Show databases

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")

for x in mycursor:
    print(x)

Creating a Table

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

Show table

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)

Alter table

mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

Delete table

mycursor.execute("DROP TABLE customers")

Insert Into Table

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

Insert Multiple Rows

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
    ('Peter', 'Lowstreet 4'),
    ('Amy', 'Apple st 652'),
    ('Hannah', 'Mountain 21'),
    ('Michael', 'Valley 345'),
    ('Sandy', 'Ocean blvd 2'),
    ('Betty', 'Green Grass 1'),
    ('Richard', 'Sky st 331'),
    ('Susan', 'One way 98'),
    ('Vicky', 'Yellow Garden 2'),
    ('Ben', 'Park Lane 38'),
    ('William', 'Central st 954'),
    ('Chuck', 'Main Road 989'),
    ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")

Select From a Table

We use the fetchall() method, which fetches all rows from the last executed statement.

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

fetchone()

The fetchone() method will return the first row of the result:

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchone()

print(myresult)

					

ติดตั้ง MySql บน CentOS 7

การดาว์นโหลดตัวติดตั้ง

ไปที่ MySQL Community Downloads แล้วเลือก MySQL Yum Repository

แล้วดาว์นโหลดไฟล์ mysql80-community-release-el7-3.noarch.rpm

ถ้าใช้ไฟล์ mysql80-community-release-el7-5.noarch.rpm ก็เป็น mysql57

การติดตั้ง

sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm

การเลือกเวอร์ชันที่จะติดตั้ง

sudo yum-config-manager --disable mysql80-community
sudo yum-config-manager --enable mysql56-community

หรือ เลือก enabled=1 ที่ไฟล์ /etc/yum.repos.d/mysql-community.repo

ตรวจสอบว่า enabled ถูกเวอร์ชันมั๊ย

yum repolist enabled | grep mysql 
sudo yum install mysql-server
-- OR --
sudo yum install mysql-community-server

Starting MySQL

sudo systemctl start mysqld
sudo systemctl status mysqld

uring the installation process, a temporary password is generated for the MySQL root user. Locate it in the mysqld.log with this command:

sudo grep 'temporary password' /var/log/mysqld.log

Configuring MySQL

sudo mysql_secure_installation

Console เชื่อมต่อ MySQL

  1. ติดตั้ง MySql.Data ผ่าน NuGet
  2. สร้างโฟลเดอร์ mysql
  3. สร้างคลาส mysql/sql.cs
  4. สร้างคลาส mysql/qry.cs
  5. แก้ไขไฟล์ App.config
  6. แก้ไขไฟล์ Program.cs

1.ติดตั้ง MySql.Data ผ่าน NuGet

PM> Install-Package MySql.Data -Version 6.9.12

2.สร้างโฟลเดอร์ mysql

3.สร้างคลาส mysql/sql.cs

using MySql.Data.MySqlClient;
using System;
using System.Configuration;
using System.Data;
using System.Reflection;
using System.Text.RegularExpressions;

namespace ConsoleApp1.mysql
{
    class sql
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        private static string connMySql = ConfigurationManager.AppSettings.Get("connMySql");


        public static DataTable ExecuteQuery(string query)
        {
            query = Regex.Replace(query, @"\s+", " ");
            log.Debug(query);

            try
            {
                using (MySqlConnection conn = new MySqlConnection(connMySql))
                {
                    conn.Open();
                    MySqlDataAdapter da = new MySqlDataAdapter(query, conn);
                    System.Data.DataSet ds = new System.Data.DataSet("Names");
                    da.Fill(ds);
                    DataTable dt = ds.Tables[0];

                    conn.Close();

                    log.Debug(string.Format("  found {0} record.", dt.Rows.Count));
                    return dt;
                }
            }
            catch (MySqlException ex)
            {
                log.Error(ex.Message);
                return null;
            }
        }

        public static DataTable ExecuteReader(string query)
        {
            //log.Info(string.Format("  '{0}'", connIIA.Split(';')[0]));

            query = Regex.Replace(query, @"\s+", " ");
            log.Debug(query);

            DataSet ds;
            try
            {
                // Open a connection
                using (MySqlConnection conn = new MySqlConnection(connMySql))
                {
                    conn.Open();
                    using (MySqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted))
                    {
                        // Create an SQL command
                        using (MySqlCommand cmd = new MySqlCommand(query, conn))
                        {
                            cmd.Transaction = trans;
                            //log.Debug(string.Format("cmd.CommandTimeout='{0}'", cmd.CommandTimeout));
                            cmd.CommandTimeout = 5000;
                            //log.Debug(string.Format("cmd.CommandTimeout='{0}'", cmd.CommandTimeout));

                            using (MySqlDataReader dr = cmd.ExecuteReader())
                            {
                                // Write the data to the console
                                //while (dr.Read())
                                //{
                                //    Console.WriteLine(dr["lotno"].ToString());
                                //}
                                ds = new System.Data.DataSet("Names");
                                while (!dr.IsClosed)
                                    ds.Tables.Add().Load(dr);
                                dr.Close();

                                DataTable dt = ds.Tables[0];

                                // Close the connection
                                conn.Close();

                                log.Debug(string.Format("  found {0} record.", dt.Rows.Count));
                                return dt;
                            }
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
                log.Error(ex.Message);
                return null;
            }
        }

        public static string ExecuteNonQuery(string query)
        {
            query = Regex.Replace(query, @"\s+", " ");
            log.Debug(query);

            try
            {
                string result = "";
                using (var conn = new MySqlConnection(connMySql))
                {
                    conn.Open();

                    using (MySqlTransaction myTrans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        // Assign transaction object for a pending local transaction
                        using (MySqlCommand myCommand = new MySqlCommand())
                        {
                            myCommand.Connection = conn;
                            myCommand.Transaction = myTrans;

                            try
                            {
                                myCommand.CommandText = query;
                                myCommand.ExecuteNonQuery();

                                myTrans.Commit();
                                result = "success";
                                log.Info(result);
                            }
                            catch (Exception ex)
                            {
                                myTrans.Rollback();
                                result = ex.Message;
                                log.Error(ex.Message);
                            }
                        }
                    }
                }
                return result;
            }
            catch (MySqlException ex)
            {
                log.Error(ex.Message);
                return null;
            }
        }

    } // end class
}

4.สร้างคลาส mysql/qry.cs

using System.Reflection;

namespace ConsoleApp1.mysql
{
    public class qry
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public static string QuerySelectData(string param1)
        {
            log.Info(string.Format("Parameter ({0})", param1));

            string query = string.Format(@"", param1);
            return query;
        }

        public static string QuerySelectData(string param1, string param2)
        {
            log.Info(string.Format("Parameter ({0}, {1})", param1, param2));

            string query = string.Format(@"", param1, param2);
            return query;
        }

    } // end class
}

5.แก้ไขไฟล์ App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>

  <appSettings>
    <add key="connMySql" value="server=xxx.xxx.xxx.xxx;database=[dbname];uid=[userid];pwd=[userpass];" />
  </appSettings>

  <system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
</configuration>

6.แก้ไขไฟล์ Program.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        static void Main(string[] args)
        {
            try
            {
                log.Info("*********************************");
                log.Info("************* BEGIN *************");
                log.Info(AppDomain.CurrentDomain.FriendlyName);


                // Check AppSettings in App.config
                if (!checkConfig())
                {
                    log.Error("Missing some config");
                    return;
                }


                string connMySql = ConfigurationManager.AppSettings.Get("connMySql");
                log.Info(string.Format("  '{0}'", connMySql.Split(';')[0]));

                mysql.sql.ExecuteReader(mysql.qry.QuerySelectData(""));


                log.Info("************** END **************");
                log.Info("");
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                log.Error(ex.ToString());
            }
        } // end main

        private static bool checkConfig()
        {
            string connMySql = ConfigurationManager.AppSettings.Get("connMySql");
            if (string.IsNullOrEmpty(connMySql))
            {
                log.Error("'connMySql' not found in App.config");
                return false;
            }

            return true;
        }
    } // end class
}

ติดตั้ง MySql บน Ubuntu 18.04

1.Installing MySQL

sudo apt update
sudo apt install mysql-server

2.Configuring MySQL

sudo mysql_secure_installation

In order to use a password to connect to MySQL as root, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:

sudo mysql

Next, check which authentication method each of your MySQL user accounts use with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;
Output
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

SELECT user,authentication_string,plugin,host FROM mysql.user;
Output
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

ทีนี้ก็จะ login ด้วย $ mysql -u root -p ได้ละ

3.Adjusting User Authentication and Privileges

$ mysql -u root -p
mysql> GRANT ALL ON *.* TO 'sammy'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

4.Testing MySQL

$ systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2020-02-06 15:32:25 +07; 17min ago
 Main PID: 3831 (mysqld)
    Tasks: 28 (limit: 4915)
   CGroup: /system.slice/mysql.service
           └─3831 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

ถ้า mysql ไม่ start ให้ start ด้วย

$ sudo systemctl start mysql 

ดูเวอร์ชันของ mysqladmin

$ sudo mysqladmin -p -u root version
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.7.29, for Linux on x86_64
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.7.29-0ubuntu0.18.04.1
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			18 min 31 sec

Threads: 1  Questions: 21  Slow queries: 0  Opens: 113  Flush tables: 1  Open tables: 106  Queries per second avg: 0.018

5.ติดตั้ง Workbench

sudo apt install mysql-workbench

สร้างโปรเจ็กส์ Web API ติดต่อ MySQL

  1. สร้างโปรเจ็กส์ด้วย Visual Studio 2019
  2. ไฟล์ต่างๆในโปรเจ็กส์
  3. เพิ่มคลาส Movie
  4. สร้างคอนโทรลเลอร์ MovieController
  5. ติดตั้ง Pomelo.EntityFrameworkCore.MySql
  6. สร้างตารางที่ MySQL
  7. ทดสอบ API
Continue reading

คิวรี MySQL

ตรวจสอบเวอร์ชันของ MySQL

SELECT VERSION();

หรือ

SHOW VARIABLES LIKE 'version%';

Limit Data Selections

ต้องการ Query รายการที่ 16 – 25

SELECT * FROM Orders LIMIT 10 OFFSET 15;

หรือ

SELECT * FROM Orders LIMIT 15, 10;