最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

mysql實(shí)現(xiàn)數(shù)據(jù)文件存儲(chǔ)到指定分區(qū)的示例代碼

 更新時(shí)間：2024年01月25日 11:39:01   作者：fong1221  
MySQL中可以使用分區(qū)表來提高數(shù)據(jù)檢索速度,本文主要介紹了mysql實(shí)現(xiàn)數(shù)據(jù)文件存儲(chǔ)到指定分區(qū)的示例代碼,具有一定的參考價(jià)值,感興趣的可以了解一下

通過rpm安裝的mysql默認(rèn)程序文件放在/usr/bin，數(shù)據(jù)文件放在/var/lib/mysql，需要將數(shù)據(jù)文件放在不同分區(qū)，保證數(shù)據(jù)量過大時(shí)將根目錄撐爆

一、關(guān)閉數(shù)據(jù)庫

[root@node1 bin]# systemctl stop mysqld
[root@node1 bin]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Wed 2024-01-10 22:23:45 CST; 16s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 103618 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 103590 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 103621 (code=exited, status=0/SUCCESS)

Jan 06 23:37:04 node1 systemd[1]: Starting MySQL Server...
Jan 06 23:37:05 node1 systemd[1]: Started MySQL Server.
Jan 10 22:23:41 node1 systemd[1]: Stopping MySQL Server...
Jan 10 22:23:45 node1 systemd[1]: Stopped MySQL Server.

二、將數(shù)據(jù)文件放到其它分區(qū)

[root@node1 bin]# cd /home/
[root@node1 home]# mkdir mysqldata
[root@node1 home]# cp -a /var/lib/mysql /home/mysqldata/
[root@node1 home]# cd mysqldata/
[root@node1 mysqldata]# ls
mysql
[root@node1 mysqldata]# ll
total 4
drwxr-x--x 7 mysql mysql 4096 Jan 10 22:23 mysql
[root@node1 mysqldata]# cd mysql/
[root@node1 mysql]# ll
total 176252
drwxr-x--- 2 mysql mysql       20 Jan  6 23:39 12233
-rw-r----- 1 mysql mysql       56 Jan  6 15:33 auto.cnf
-rw------- 1 mysql mysql     1680 Jan  6 15:33 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Jan  6 15:33 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Jan  6 15:33 client-cert.pem
-rw------- 1 mysql mysql     1680 Jan  6 15:33 client-key.pem
-rw-r----- 1 mysql mysql      790 Jan 10 22:23 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Jan 10 22:23 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 10 22:23 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan  6 15:33 ib_logfile1
drwxr-x--- 2 mysql mysql    40960 Jan  6 16:36 ivsom
drwxr-x--- 2 mysql mysql     4096 Jan  6 15:33 mysql
drwxr-x--- 2 mysql mysql     8192 Jan  6 15:33 performance_schema
-rw------- 1 mysql mysql     1680 Jan  6 15:33 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Jan  6 15:33 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Jan  6 15:33 server-cert.pem
-rw------- 1 mysql mysql     1680 Jan  6 15:33 server-key.pem
drwxr-x--- 2 mysql mysql     8192 Jan  6 15:33 sys

三、修改配置文件

原來配置文件

[root@node1 mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
user=root
datadir=/var/lib/mysql   //數(shù)據(jù)文件目錄
socket=/var/lib/mysql/mysql.sock   //sock目錄
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

修改后的配置文件

[root@node1 mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
user=root
datadir=/home/mysqldata/mysql
socket=/home/mysqldata/mysql/mysql.sock
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8
socket=/home/mysqldata/mysql/mysql.sock

[mysql]
default-character-set=utf8
socket=/home/mysqldata/mysql/mysql.sock

四、啟動(dòng)數(shù)據(jù)庫

[root@node1 mysql]# systemctl start mysqld
[root@node1 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2024-01-10 22:42:29 CST; 12s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 126422 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 126385 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 126425 (mysqld)
    Tasks: 27
   Memory: 191.0M
   CGroup: /system.slice/mysqld.service
           └─126425 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jan 10 22:42:27 node1 systemd[1]: Starting MySQL Server...
Jan 10 22:42:29 node1 systemd[1]: Started MySQL Server.

五、驗(yàn)證數(shù)據(jù)文件更新分區(qū)是否成功

[root@node1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)

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> SHOW VARIABLES LIKE 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /home/mysqldata/mysql/ |  //確實(shí)已經(jīng)更新
+---------------+------------------------+
1 row in set (0.05 sec)

mysql>

到此這篇關(guān)于mysql實(shí)現(xiàn)數(shù)據(jù)文件存儲(chǔ)到指定分區(qū)的示例代碼的文章就介紹到這了,更多相關(guān)mysql 文件存儲(chǔ)到指定分區(qū)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • MySQL慢查詢分析與優(yōu)化全過程

    MySQL慢查詢分析與優(yōu)化全過程

    本文介紹了MySQL慢查詢優(yōu)化的完整閉環(huán),從開啟慢查詢?nèi)罩?、使用pt-query-digest定位TOP慢SQL、借助EXPLAIN診斷執(zhí)行計(jì)劃、給出優(yōu)化案例,到搭建Prometheus+Grafana監(jiān)控體系,針對(duì)全表掃描、深分頁、隱式類型轉(zhuǎn)換、OR條件和聚合統(tǒng)計(jì)等個(gè)常見問題,需要的朋友可以參考下
    2026-05-05
  • MySQL中Navicat自動(dòng)備份的實(shí)現(xiàn)

    MySQL中Navicat自動(dòng)備份的實(shí)現(xiàn)

    本文主要介紹了MySQL中Navicat自動(dòng)備份的實(shí)現(xiàn),包括手動(dòng)備份和自動(dòng)定時(shí)備份,文中通過圖文示例介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2025-03-03
  • MySQL的緩存策略方式

    MySQL的緩存策略方式

    MySQL緩存方案主要用于減輕數(shù)據(jù)庫讀寫壓力,通過使用Redis緩存用戶定義的熱點(diǎn)數(shù)據(jù),用戶可以直接從緩存中獲取數(shù)據(jù),文章還討論了如何通過讀寫分離、連接池和異步連接等技術(shù)提升MySQL的訪問性能,此外,還探討了緩存方案中的一致性問題、讀寫策略以及緩存穿透
    2024-09-09
  • mysql啟動(dòng)錯(cuò)誤之mysql啟動(dòng)報(bào)1067錯(cuò)誤解決方法

    mysql啟動(dòng)錯(cuò)誤之mysql啟動(dòng)報(bào)1067錯(cuò)誤解決方法

    MYSQL啟動(dòng)報(bào)1067錯(cuò)誤，系統(tǒng)日志中是“服務(wù) mysql 意外停止” Mysql日志中則是：Plugin 'FEDERATED' is disabled,下面給出解決方法
    2014-02-02
  • MySQL如何使用視圖插入修改刪除數(shù)據(jù)

    MySQL如何使用視圖插入修改刪除數(shù)據(jù)

    視圖除了進(jìn)行查詢記錄外,也可以利用視圖進(jìn)行插入、更新、刪除記錄的操作,減少對(duì)基表中信息的直接操作,提高了數(shù)據(jù)的安全性,下面這篇文章主要給大家介紹了關(guān)于MySQL如何使用視圖插入修改刪除數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下
    2023-06-06
  • 關(guān)于MySQL中“Insert into select“ 的死鎖情況分析

    關(guān)于MySQL中“Insert into select“ 的死鎖情況分析

    這篇文章主要介紹了關(guān)于MySQL中“Insert into select“ 的死鎖情況分析,死鎖是指兩個(gè)或者多個(gè)事務(wù)在同一資源上的相互占用，并請(qǐng)求鎖定對(duì)方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象,需要的朋友可以參考下
    2023-05-05
  • 踩坑MySQL UNION和ORDER BY混用的問題及解決

    踩坑MySQL UNION和ORDER BY混用的問題及解決

    MySQL中UNION合并多個(gè)子集時(shí),內(nèi)部ORDER BY可能失效,解決方法：各子集添加LIMIT,外層再包裹SELECT并使用ORDER BY,確保整體排序正確
    2025-09-09
  • mysql優(yōu)化取隨機(jī)數(shù)據(jù)慢的方法

    mysql優(yōu)化取隨機(jī)數(shù)據(jù)慢的方法

    mysql取隨機(jī)數(shù)據(jù)慢,怎么辦?下面小編與大家一起來看看mysql取隨機(jī)數(shù)據(jù)慢優(yōu)化的過程。
    2013-11-11
  • MySQL錯(cuò)誤代碼2058和2059的解決辦法

    MySQL錯(cuò)誤代碼2058和2059的解決辦法

    這篇文章主要介紹了MySQL錯(cuò)誤代碼2058和2059的解決辦法,2058和2059的錯(cuò)誤碼核心都是你用的客戶端工具和mysql版本的密碼插件不匹配,下面就來介紹一下解決方法,感興趣的可以了解一下
    2025-03-03
  • 正確使用MySQL INSERT INTO語句

    正確使用MySQL INSERT INTO語句

    MySQL數(shù)據(jù)庫是一種小型關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，MySQL數(shù)據(jù)庫的優(yōu)化是MySQL數(shù)據(jù)庫操作過程中非常重要的工作，MySQL數(shù)據(jù)庫的優(yōu)化能夠?qū)崿F(xiàn)MySQL數(shù)據(jù)庫操作的簡便。
    2015-08-08

最新評(píng)論

余姚市| 南安市| 大埔县| 莱州市| 天柱县| 个旧市| 井陉县| 达孜县| 遂溪县| 通化市| 且末县| 西城区| 汉源县| 曲靖市| 洛南县| 察雅县| 铜鼓县| 囊谦县| 左贡县| 锦州市| 神木县| 岑巩县| 巴彦县| 腾冲县| 新化县| 射阳县| 南华县| 新河县| 泸州市| 偏关县| 汤阴县| 古交市| 射阳县| 宁远县| 长春市| 沂水县| 辉南县| 中牟县| 富蕴县| 女性| 静安区|