MySQL数据库的安装

  1. 注意事项:
  • 注意操作系统平台(32位,64位)
  • 注意安装M有SQL的版本(企业版,社区版,MariaDB(centos默认可能安装这个版本))
  • 注意安装后避免yum自动更新(如果是网络安装,系统安装包会和yum建立一个关联关系,会自动跟随yum更新,安装完成后,需要删除)
  • 注意数据库的安全性(合理的访问权限,密码复杂度)
  1. 安装:
  • 查看系统结构:
    arch –> x86_64
  • 查看系统版本:
    cat /etc/redhat-release –> CentOS Linux release 8.3.2011
  • 下载安装包:
    dev.mysql.com 可以下载安装包
    或者通过网络安装,先下载 网络安装工具:
    wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
    安装mysql源:
    yum localinstall mysql57-community-release-el7-8.noarch.rpm
  • 安装:
    yum install mysql-community-server
    如果报错: 报错 Error: Unable to find a match: mysql-community-server

    image.png

    解决办法:

  • 禁用本地的Mysql模块:
    yum module disable mysql
  • 再次安装:
    yum install mysql-community-server
  1. 安装完成后设置:
  • 取消自动更新:
    yum remove mysql57-community-release.noarch
  • 启动mysql:
    systemctl start mysqld
  • 设置随开机自动启动:
    systemctl enable mysqld
    systemctl daemon-reload
  • 查看mysql服务状态:
    systemctl status mysqld
  • 检查mysql是否为MariaDB
    rpm -qa | grep -i ‘mysql’
  • 可选设置: 端口开放:
    firewall-cmd –zone=public –add-port=3306/tcp –permanent
    firewall-cmd –reload
  1. 登录mysql:
  • 查看mysql初始密码:
    grep ‘password’ /var/log/mysqld.log | head -1
    或者:
    grep ‘temporary password’ /var/log/mysqld.log
    默认密码复杂度要求:

    image.png
  • 修改密码:
    alter user ‘root’@’localhost’ identified by ‘New_password12!’;
  • 查看密码复杂度:
    show variables like ‘%password%’;
  • 修改密码复杂度:
    set global validate_password_police=0;
  1. MySQL 字符集 charset
  • utf8 (3位字节) 不等于 utf-8 (4字节)
  • utf8mb4 相当于 utf-8
  • 查看字符集:
    show variables like ‘%character%’;

    image.png
  • 查看校对规则:
    show variables like ‘%collation_%’;

    image.png
  1. 修改字符集
  • vim /etc/my.cnf

[client]
default_character_set = utf8mb4
[mysql]
default_character_set = utf8mb4
[mysqld]
interactive_timeout = 28800 # 针对交互式连接 超时时间
wait_timeout=28800 # 针对非交互式连接超时时间
max_connections=1000 # MySQL的最大连接数
character_set_server = utf8mb4 # MySQL字符集设置
init_connect = ‘SET NAMES utf8mb4’ # 服务器针对每个连接的客户端执行的字符串
character_set_client_handshake = FALSE
collation_server = utf8mb4_unicode_ci # 校对规则 _ci 大小写不敏感

  1. 连接MySQL
  • MySQLdb 适用于 python2, MYSQL 不高于5.5, python不高于2.7
    在Python3中 MySQLdb包叫做 mysqlclient,加载的依然是MySQLdb,如下:
    pip install mysqlclient
    import MySQLdb
  • 其他DB-API:
    pip install pymysql # 流行度最高
    pip install mysql-connector-python # MySQL官方
  • 使用ORM: 对象映射关系,对DB-API的一种扩展,不可以单独使用,需要与DB-API结合。 对DB-API的一种抽象,可以应用于多种语言,多种数据库。
    pip install sqlalchemy
  • 代码示例:
  • pymsql:
#!/usr/bin/python3
# PyMYSQL 连接 MySQL 数据库
# pip3 install PyMySQL
import pymysql
# 打开数据库连接
# mysql > create database testdb;
# mysql > use testdb;
# mysql > GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'%' IDENTIFIED BY 'testpass';

db = pymysql.connect('192.168.246.129', 'testuser', 'testpass', 'testdb')

try:
    with db.cursor() as cursor:
        sql = 'SELECT VERSION()'
        cursor.execute(sql)
        result = cursor.fetchone()
    db.commit()
except Exception as e:
    print(f'fetch error {e}')
finally:
    db.close()
print(f'Database version: {result} ')
  • sqlalchemy, 使用元数据
#!/usr/bin/python3
# sqlalchemy 连接数据库
# pip install sqlalchemy
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('mysql+pymysql://testuser:testpass@192.168.246.129:3306/testdb', echo=True)

meta = MetaData(engine)

book = Table('book', meta,
             Column('id', Integer, primary_key=True),
             Column('name', String(20)))
author = Table('author', meta,
               Column('id', Integer, primary_key=True),
               Column('book_id', None, ForeignKey('book.id')),
               Column('author_name', String(200), nullable=False))
try:
    meta.create_all()
except Exception as e:
    print(f'create error {e}')
  • sqlalchemy, 使用ORM
#!/usr/bin/python3
# sqlalchemy 连接数据库
# pip install sqlalchemy
from sqlalchemy import Integer, Column, String, ForeignKey, DateTime, create_engine
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class Book_Table(Base):
    __tablename__ = 'bookorm'
    book_id = Column(Integer(), primary_key=True)
    book_name = Column(String(50), index=True)

class Author_table(Base):
    __tablename__ = 'authororm'
    user_id = Column(Integer(), primary_key=True)
    username = Column(String(15), nullable=False, unique=True)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)

dburl = 'mysql+pymysql://testuser:testpass@192.168.246.129:3306/testdb?charset=utf8mb4'

engine = create_engine(dburl, echo=True, encoding='utf-8')
Base.metadata.create_all(engine)

文章来源于互联网:Python 操作 mysql,安装,设置及连接

发表评论