第一階段：

在未上線之前，數(shù)據(jù)庫(kù)的配置工作并不十分復(fù)雜，而且數(shù)據(jù)量也比較少，所以用了mysqlworkbench手工完成每個(gè)類(lèi)的數(shù)據(jù)填充。

第二階段：

為了滿足上線要求，必須要將數(shù)據(jù)庫(kù)和程序一起移到：服務(wù)器上，所有東西都未配置，故做了以下工作：

1）查看該vps系統(tǒng)：

uname -a或lsb_release -a即可查看

2）我的vps為ubuntu，所以可以解釋清楚為什么有apt-get而沒(méi)有yum，但是卻不能用。

然后在嘗試了裝rpm之后意識(shí)到這不是centos，于是在同學(xué)的提醒下，對(duì)apt的源進(jìn)行更新。

隨后，正常安裝mysql。

apt-get install mysql-client, mysql-server

(有的人說(shuō)要裝一個(gè)libmysqlclient-dev，但是我之前都沒(méi)裝，數(shù)據(jù)庫(kù)運(yùn)行正常，寫(xiě)這篇博客的時(shí)候才開(kāi)始裝)

3）在裝完mysql后便是對(duì)用戶等的一些配置：

--- > 在看有無(wú)裝成功，方法很多，我直接mysql，按tab鍵，有補(bǔ)全，即裝成

忘記什么時(shí)候設(shè)置密碼了，中間不是很復(fù)雜

--- > 以root登錄：mysql -u root -p 然后輸入密碼

--- > 創(chuàng)建新的用戶，為了使權(quán)限分清，安全起見(jiàn)：

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

這里面大小寫(xiě)沒(méi)關(guān)系，%代表任意位置，可以換成localhost或是指定ip，由于我們是本地程序訪問(wèn)，所以設(shè)置為localhost。

--- > 設(shè)置用戶權(quán)限：

grant all privileges on 想授權(quán)的數(shù)據(jù)庫(kù).* to 'user1'@'%';

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

--- > 更改密碼：

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

這是因?yàn)橛脩粜畔⒃趍ysql下的user表中存儲(chǔ)，而加密方式用password函數(shù)即可

第三階段：

這個(gè)部分主要是寫(xiě)代碼，讓創(chuàng)建數(shù)據(jù)庫(kù)等操作簡(jiǎn)單化，為了應(yīng)對(duì)未來(lái)的自動(dòng)化部署

下面是我的代碼，很簡(jiǎn)單，解釋也較清晰，我就不多廢話了

    # -*- coding: utf-8 -*-
    # @Time    : 2018/8/29 上午10:52
    # @Author  : shijie
    # @Email   : lsjfy0411@163.com
    # @File    : docker_mysql.py
    # @Software: PyCharm
    '''
    為了標(biāo)準(zhǔn)化，在設(shè)計(jì)初始階段，主要分三個(gè)模塊用來(lái)測(cè)試；
    這是第一個(gè)模塊，即，與數(shù)據(jù)庫(kù)交互的功能部分；
    數(shù)據(jù)庫(kù)名：fanuc
    表名：session
    用戶名：fanuc
    密碼：fanuc123
    session表的結(jié)構(gòu)為：
    請(qǐng)盡量按照此標(biāo)準(zhǔn)進(jìn)行
    '''

    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語(yǔ)句構(gòu)建
    def createSql():
        sql = "CREATE DATABASE IF NOT EXISTS "

    #連接

    def connectMysql(config):
        '''
        這里連接，給個(gè)config就行，不管是創(chuàng)建數(shù)據(jù)庫(kù)還是數(shù)據(jù)表
        :param config:
        :return:
        '''
        return pymysql.connect(**config)


    #新建數(shù)據(jù)庫(kù)
    def createDB(con,DBname):
        '''

        :param con: 這是針對(duì)連接的，不是針對(duì)數(shù)據(jù)表的
        :param DBname: 字符串，要建的數(shù)據(jù)庫(kù)名
        :return:
        '''
        sql = "CREATE DATABASE IF NOT EXISTS " + DBname
        cur = con.cursor()
        try:
            cur.execute(sql)
            print("數(shù)據(jù)庫(kù)%s創(chuàng)建成功"%DBname)
        except:
            print("已存在")

    #新建數(shù)據(jù)表
    def createTable(db,TableName):
        '''
        傳入新的db類(lèi)和表名
        :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("數(shù)據(jù)表%s插入成功"%TableName)
        except:
            print("失敗，可能%s該表已存在"%TableName)
    #插入數(shù)據(jù)


    def insertDB(DB):
        cur = DB.cursor()
        cur.execute("")
    #查詢數(shù)據(jù)


    #刪除數(shù)據(jù)庫(kù)
    def dropDB(con,DBname):
        sql = "DROP DATABASE IF EXISTS " + DBname
        cur = con.cursor()
        try:
            cur.execute(sql)
            print("數(shù)據(jù)庫(kù)%s刪除成功"%DBname)
        except :
            print("已刪除")

    #刪除數(shù)據(jù)表
    def dropTable(db,TableName):
        sql = "DROP TABLE IF EXISTS " + TableName
        cur = db.cursor()
        try:
            cur.execute(sql)
            print("數(shù)據(jù)表%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 =

這只是一部分，還有插入的部分我再修改中，所以暫時(shí)不加進(jìn)來(lái)。

第四階段：

為了緊急上線，因?yàn)槲抑暗牧硪环N思路是利用docker去規(guī)模部署的，但是時(shí)間較短，一天還沒(méi)怎么學(xué)會(huì)，師兄說(shuō)得抓緊，所以就在本地?cái)?shù)據(jù)庫(kù)和vps之間要做個(gè)數(shù)據(jù)庫(kù)導(dǎo)出導(dǎo)入工作。

這里碰到一些坑：

-------->> 我的mac默認(rèn)的編碼格式為utf8m64，collation模式為utf8mb4_0900_ai_ci在導(dǎo)出的sql或txt文件中配置也是這么寫(xiě)的，但在vps-ubuntu上導(dǎo)入時(shí)，無(wú)法識(shí)別，出現(xiàn)錯(cuò)誤。

問(wèn)題解決：將導(dǎo)出的文件中的utf8m64替換為utf8，將utf8mb4_0900_ai_ci替換為utf8——unicode_ci。

-------->> 另一個(gè)坑是我的mac上的mysql基本配置文件my-default.cnf不見(jiàn)了，所以有下載了一個(gè)，重新更名為my.cnf，并放在了etc文件夾下，網(wǎng)上有一個(gè)配置文件，有人下了坑，反注釋可能會(huì)被反連接到，所以不敢輕易嘗試這么做。我放的這個(gè)文件沒(méi)問(wèn)題，已經(jīng)檢查過(guò)了。

# 

# 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 

  #innodb_log_buffer_size = 8M 

  #innodb_flush_log_at_trx_commit = 1 

  #innodb_lock_wait_timeout = 50 



    [mysqldump] 

    quick 

    max_allowed_packet = 16M 



      [mysql] 

      no-auto-rehash 

      # Remove the next comment character if you are not familiar with SQL 

      #safe-updates 

      default-character-set=utf8 



    [myisamchk] 

    key_buffer_size = 20M 

    sort_buffer_size = 20M 

    read_buffer = 2M 

    write_buffer = 2M 



      [mysqlhotcopy] 

      interactive-timeout

'''

第五階段

在上述基礎(chǔ)之上，完成對(duì)數(shù)據(jù)的遷移，有幾種方法：mysql導(dǎo)入、source導(dǎo)入和load data導(dǎo)入，我選擇用source。
1）在新機(jī)器上創(chuàng)建和原mysql一樣的用戶名和密碼及編碼

剛裝的mysql是不需要密碼的，故更改即可，我們使用下邊的命令

 update user set password=PASSWORD("newpassword") where user='root';

創(chuàng)建DB并設(shè)置編碼

create database 'db';
use 'db';
set names utf8;

最后一步，導(dǎo)入數(shù)據(jù)

source path

完成