mysql的一些项目_Mysql在本项目中的一些坑和用法-CSDN博客

本文链接：https://blog.csdn.net/weixin_42442653/article/details/113684937

本文详述了从本地MySQL数据库配置到服务器迁移的全过程，包括查看VPS系统信息、安装MySQL、创建用户和权限分配，以及通过Python代码简化数据库操作。在迁移过程中，解决了编码不兼容和配置文件问题，最终实现数据导入。

摘要由CSDN通过智能技术生成

第一阶段：

在未上线之前，数据库的配置工作并不十分复杂，而且数据量也比较少，所以用了mysqlworkbench手工完成每个类的数据填充。

第二阶段：

为了满足上线要求，必须要将数据库和程序一起移到：服务器上，所有东西都未配置，故做了以下工作：

1)查看该vps系统：

uname -a或lsb_release -a即可查看

2)我的vps为ubuntu，所以可以解释清楚为什么有apt-get而没有yum，但是却不能用。

然后在尝试了装rpm之后意识到这不是centos，于是在同学的提醒下，对apt的源进行更新。

随后，正常安装mysql。

apt-get install mysql-client, mysql-server

(有的人说要装一个libmysqlclient-dev，但是我之前都没装，数据库运行正常，写这篇博客的时候才开始装)

3)在装完mysql后便是对用户等的一些配置：

--- > 在看有无装成功，方法很多，我直接mysql，按tab键，有补全，即装成

忘记什么时候设置密码了，中间不是很复杂

--- > 以root登录：mysql -u root -p 然后输入密码

--- > 创建新的用户，为了使权限分清，安全起见：

CREATE USER 'user1'@'%' IDENTIFIED BY 'password';

这里面大小写没关系，%代表任意位置，可以换成localhost或是指定ip，由于我们是本地程序访问，所以设置为localhost。

--- > 设置用户权限：

grant all privileges on 想授权的数据库.* to 'user1'@'%';

all可以换位其他的select，delete，update, drop, create等

--- > 更改密码：

update mysql.user set password=password('新密码') where user='user1';

这是因为用户信息在mysql下的user表中存储，而加密方式用password函数即可

第三阶段：

这个部分主要是写代码，让创建数据库等操作简单化，为了应对未来的自动化部署

下面是我的代码，很简单，解释也较清晰，我就不多废话了

# -*- coding: utf-8 -*-

# @Time : 2018/8/29 上午10:52

# @Author : shijie

# @Email : lsjfy0411@163.com

# @File : docker_mysql.py

# @Software: PyCharm

'''

为了标准化，在设计初始阶段，主要分三个模块用来测试；

这是第一个模块，即，与数据库交互的功能部分；

数据库名：fanuc

表名：session

用户名：fanuc

密码：fanuc123

session表的结构为：

请尽量按照此标准进行

'''

import pymysql

import copy

#配置信息]

config = {

'host': '127.0.0.1',

'port': 3306,

# 'db':'fanuc_docker',

'user': 'root',

'passwd': '',

# 'charset':'utf8mb4',

'cursorclass':pymysql.cursors.DictCursor

}

def changeConfig(config,dbName):

ConfigForInsert = copy.deepcopy(config)

ConfigForInsert['db'] = dbName

return ConfigForInsert

#sql语句构建

def createSql():

sql = "CREATE DATABASE IF NOT EXISTS "

#连接

def connectMysql(config):

'''

这里连接，给个config就行，不管是创建数据库还是数据表

:param config:

:return:

'''

return pymysql.connect(**config)

#新建数据库

def createDB(con,DBname):

'''

:param con: 这是针对连接的，不是针对数据表的

:param DBname: 字符串，要建的数据库名

:return:

'''

sql = "CREATE DATABASE IF NOT EXISTS " + DBname

cur = con.cursor()

try:

cur.execute(sql)

print("数据库%s创建成功"%DBname)

except:

print("已存在")

#新建数据表

def createTable(db,TableName):

'''

传入新的db类和表名

:param db:

:param TableName:

:return:

'''

sql = """CREATE TABLE %s(

request_data VARCHAR(3000) NOT NULL,

response_data VARCHAR(3000),

functions VARCHAR(45),

session_id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

data DATE)"""%TableName

# print(sql)

cur = db.cursor()

try:

cur.execute(sql)

print("数据表%s插入成功"%TableName)

except:

print("失败，可能%s该表已存在"%TableName)

#插入数据

def insertDB(DB):

cur = DB.cursor()

cur.execute("")

#查询数据

#删除数据库

def dropDB(con,DBname):

sql = "DROP DATABASE IF EXISTS " + DBname

cur = con.cursor()

try:

cur.execute(sql)

print("数据库%s删除成功"%DBname)

except :

print("已删除")

#删除数据表

def dropTable(db,TableName):

sql = "DROP TABLE IF EXISTS " + TableName

cur = db.cursor()

try:

cur.execute(sql)

print("数据表%s删除成功"%TableName)

except :

print("该表已删除")

if __name__ == '__main__':

con = connectMysql(config)

createDB(con,"fanucdocker")

dbconfig = changeConfig(config, 'fanucdocker')

db = connectMysql(dbconfig)

createTable(db,'sessionNew')

# dropTable(db,'sessionNew')

# dropTable(db,'session')

# dropDB(con,"docker")

# dropDB(con,"fanucdocker")

# sql =

这只是一部分，还有插入的部分我再修改中，所以暂时不加进来。

第四阶段：

为了紧急上线，因为我之前的另一种思路是利用docker去规模部署的，但是时间较短，一天还没怎么学会，师兄说得抓紧，所以就在本地数据库和vps之间要做个数据库导出导入工作。

这里碰到一些坑：

-------->> 我的mac默认的编码格式为utf8m64，collation模式为utf8mb4_0900_ai_ci在导出的sql或txt文件中配置也是这么写的，但在vps-ubuntu上导入时，无法识别，出现错误。

问题解决：将导出的文件中的utf8m64替换为utf8，将utf8mb4_0900_ai_ci替换为utf8——unicode_ci。

-------->> 另一个坑是我的mac上的mysql基本配置文件my-default.cnf不见了，所以有下载了一个，重新更名为my.cnf，并放在了etc文件夹下，网上有一个配置文件，有人下了坑，反注释可能会被反连接到，所以不敢轻易尝试这么做。我放的这个文件没问题，已经检查过了。

# Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] default-character-set=utf8 #password = your_password port = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] character-set-server=utf8 init_connect='SET NAMES utf8 port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M character-set-server=utf8 init_connect='SET NAMES utf8' # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # binary logging format - mixed recommended binlog_format=mixed # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, # MASTER_USER=, MASTER_PASSWORD=; # # where you replace,,by quoted strings and #by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host =# # The username the slave will use for authentication when connecting # to the master - required #master-user =# # The password the slave will authenticate with when connecting to # the master - required #master-password =# # The port the master is listening on. # optional - defaults to 3306 #master-port =

# binary logging - not required for slaves, but recommended

#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /usr/local/mysql/data

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /usr/local/mysql/data

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 16M

#innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 5M