MySQL工作經驗

leneing發表於2018-12-28
MySql

  • 以下是根據工作中遇到各種場景用到的一些Mysql用法，比較實用，基本是語法之外的一些東西。

　　修改賬戶密碼

　　1.開啟Mysql控制檯，輸入原密碼；

　　2.輸入以下語法：mysql> set password for root@localhost = password(`123456`)。

 

　　忘了root密碼，怎麼辦？

　　1. 關閉正在執行的MySQL服務；

　　2. 開啟DOS視窗，轉到mysqlin目錄；

　　3. 輸入mysqld –skip-grant-tables 回車； –skip-grant-tables 的意思是啟動MySQL服務的時候跳過許可權表認證

　　4. 再開一個DOS視窗（因為剛才那個DOS視窗已經不能動了），轉到mysqlin目錄；

　　5. 輸入mysql回車，如果成功，將出現MySQL提示符 >；

　　7. 連線許可權資料庫： use mysql；

　　8. 改密碼：update user set password=password(“123″) where user=”root”;（別忘了最後加分號）；

　　9. 重新整理許可權（必須步驟）：flush privileges；

　　10. 退出 quit；

　　11. 登出系統，再進入，使用使用者名稱root和剛才設定的新密碼123登入。

 

　　徹底解除安裝mysql

　　1. 控制皮膚裡解除安裝Mysql；

　　2. 刪除C:Program FilesMySQL資料夾(服務安裝位置)；

　　3. 刪除C:Documents and SettingsAll UsersApplication DataMySQL資料夾(資料庫存貯位置)；

　　4. regedit開啟登錄檔， 刪除HKEY_LOCAL_MACHINESYSTEMControlSet001ServicesEventlogApplicationMySQL；HKEY_LOCAL_MACHINESYSTEMControlSet002ServicesEventlogApplicationMySQL；HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplicationMySQL(登錄檔非必要步驟，實在沒辦法就刪)； 

 

　　一臺伺服器安裝多個mysql服務提供給多個專案作測試環境

　　1. 停止mysql服務；

　　2. 將資料庫安裝目錄和資料庫存貯目錄都複製到另外一個路徑並重新命名；

　　3. 複製出來的mysql資料夾的my.ini所有的預設路徑改成新的路徑，並將所有的port=3306改成3307，有兩個地方，具體如下：

    修改如下內容: 
    [client] 
    port=3307 #第一個資料庫的預設埠是3306 這裡需要另外啟用一個埠 

    # The TCP/IP Port the MySQL Server will listen on 
    port=3307 

    # Path to installation directory. 
    basedir="E:MySQL2MySQL Server 5.5" #第二個資料庫服務安裝位置 

    # Path to the database root 
    datadir="E:MySQL2MySQL Server 5.5data" #第二個資料庫存放位置

　　4. 建立服務：(重點：先進入到MySql2的bin目錄下，最好先把原來的mysql環境變數刪除，防止mysqld用的是之前的資料夾裡的)mysqld install MySQL2 –defaults-file=”E:MySQL2MySQL Server 5.5my.ini” 

　　5.修改登錄檔HKEY_LOCAL_MACHINE–>SYSTEM–>CurrentControlSet–>Services 找到剛才建立的MySQL2，

　　   將ImagePath修改成如下”E:MySQL2MySQL Server 5.5inmysqld” –defaults-file=”E:MySQL2MySQL Server5.5my.ini” MySQL2

　　6. 啟動新服務

　　7.1 情況1：對於原資料庫data沒有一起拷貝，那麼需要修改資料庫密碼，新的資料庫預設密碼為空，先使用127.0.0.1登入後執行

    1. use mysql；
    2. update user set password=password(`root`) where user=`root`；
    3. FLUSH PRIVILEGES；
    4. GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY `root` WITH GRANT OPTION;

 　　7.2情況2：直接資料庫存貯data都一起拷貝了，那麼使用者表應該也在裡面，就直接用老的資料庫密碼登入就行。

　　刪除mysql服務

　　 cmd：sc delete mysql，完了之後重啟電腦生效。

 

　　mysql主從備份

　　 1. 先看主機的

　　 1.1 編輯my.ini檔案，找到節點[mysqld]，增加三條：

　　　　log-bin=mysql-bin

　　　　server-id=起一個主機伺服器id

　　　　binlog-do-db=資料庫名字

　　1.2 重啟服務

　　1.3.pushd C:Program FilesMySQLMySQL Server 5.5in

　　1.4.mysql -h192.168.3.22 -uroot -proot

　　1.5.GRANT REPLICATION SLAVE ON *.* to `mysync`@`%` identified by `123456`;

　　1.6.show master status; 通過這一步獲取到檔名和日誌索引[配置到從機需要]

　　

　　2.再看從機的

　　2.1  編輯my.ini檔案，找到節點[mysqld]，增加一條：

　　　　server-id=從機伺服器id

　　2.2 重啟服務

　　2.3 pushd C:Program FilesMySQLMySQL Server 5.5in

　　2.4.mysql -h192.168.3.180 -uroot -proot

　　2.5.change master to master_host=`192.168.3.22`,master_user=`mysync`,master_password=`123456`,master_log_file=`mysql-bin.000004`,master_log_pos=107; 

　　2.6.start slave;

　　2.7.show slave statusG

 

　　3. 確認一致性

　　3.1 對比Master_Log_File 和 Relay_Master_Log_File 是否一致；

　　3.2 對比Read_Master_Log_Pos 和 Exec_Master_Log_Pos 是否一致；

　　3.3 Slave_IO_Running: Yes；

　　3.4 Slave_SQL_Running: Yes；

　　親測斷網斷電都可恢復同步；

 

　　賬戶許可權和IP限制

    //進入mysql控制檯，刪除root遠端訪問許可權，僅允許本機訪問
    delete from user where user =`root` and host =`%`;

    //儲存
    flush privileges;

    //建立mysql只讀賬戶
    grant select on *.*  to readonly@"%" identified by     "sudy@gjwg123456";

    //建立ip限制的賬戶
    grant all privileges on *.* to `root`@`192.100.50.73` identified by `        sudy@gjwg123456`;

    //儲存
    flush privileges;

　　

　　對N張資料結構相同的表批量增加欄位

 　　有時候根據模板表讓系統自動建立子表用於緩解大資料壓力和拆分業務，需要增加欄位的時候，就需要對上百張表結構進行改動。

　　 利用CONCAT先批量生成語句，然後全選執行即可。

　　 1. 增加欄位：SELECT CONCAT(`alter table db_gpsround.`,table_name,` add sjlsh int(1) default 0 COMMENT `審計流水號，0未入庫，1已入庫`;`) as hh FROM information_schema.tables WHERE table_schema = `db_gpsround`;

　　 2. 增加索引：SELECT CONCAT(`alter table db_gps_xlbh.`,table_name,` add index zjgxsj(zjgxsj);`) as hh FROM information_schema.tables WHERE table_schema = `db_gps_xlbh`;

 

　　利用mysqldump備份與還原資料庫

　　備份整個資料庫
　　D:Program FilesMySQLMySQL Server 5.5in>mysqldump -uroot -p123 dbparking > E:mysql_ghostdbparking.sql
　
　　還原整個資料庫
　　D:Program FilesMySQLMySQL Server 5.5in>mysql -uroot -p123 testpark < E:mysql_ghostdbparking.sql

　　備份表
　　D:Program FilesMySQLMySQL Server 5.5in>mysqldump -uroot -p123 dbparking 表1 表2 表3> E:mysql_ghostdbparking.sql
　　
　　還原表
　　同上

 

　　定期備份與刪除備份

    //利用mysqldump備份，利用winrar壓縮，注意備份時會影響其他程式，儘量選擇在晚上備份
    @echo on  
    　　cd D:Program FilesMySQLMySQL Server 5.5in 
    　　set Ymd=%date:~,4%%date:~5,2%%date:~8,2%
    　　set filename=db_dbjs_%Ymd%
    　　mysqldump.exe db_dbjs -uroot -psudy1223@2016 -R>     D:DBServerIdb_backdb_dbjs%filename%.sql  
    　　c:   
    　　cd C:Program FilesWinRAR
    　　WinRAR a -m5 -df -ep     D:DBServerIdb_backdb_dbjs%filename%.rar      D:DBServerIdb_backdb_dbjs%filename%.sql
    @echo off
    //刪除3天前的備份檔案
    forfiles /p "D:DBServerIdb_back" /s /m *.* /d -3 /c "cmd /c del @path"

相關文章