mysql實(shí)現(xiàn)數(shù)據(jù)文件存儲到指定分區(qū)的示例代碼
更新時間：2024年01月25日 11:39:01 作者：fong1221
MySQL中可以使用分區(qū)表來提高數(shù)據(jù)檢索速度,本文主要介紹了mysql實(shí)現(xiàn)數(shù)據(jù)文件存儲到指定分區(qū)的示例代碼,具有一定的參考價值,感興趣的可以了解一下
通過rpm安裝的mysql默認(rèn)程序文件放在/usr/bin，數(shù)據(jù)文件放在/var/lib/mysql，需要將數(shù)據(jù)文件放在不同分區(qū)，保證數(shù)據(jù)量過大時將根目錄撐爆
一、關(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
四、啟動數(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.五、驗證數(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ù)文件存儲到指定分區(qū)的示例代碼的文章就介紹到這了,更多相關(guān)mysql 文件存儲到指定分區(qū)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
相關(guān)文章
使用MySQL MySqldump命令導(dǎo)出數(shù)據(jù)時的注意事項
這篇文章主要介紹了使用MySQL MySqldump命令導(dǎo)出數(shù)據(jù)時的注意事項,很實(shí)用的經(jīng)驗總結(jié),需要的朋友可以參考下2014-07-07
mysql如何設(shè)置主從數(shù)據(jù)庫的同步
這篇文章主要介紹了mysql如何設(shè)置主從數(shù)據(jù)庫的同步問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10
MySQL定位并優(yōu)化慢查詢sql的詳細(xì)實(shí)例
mysql記錄下查詢超過指定時間的語句,被稱為慢查詢,下面這篇文章主要給大家介紹了關(guān)于MySQL定位并優(yōu)化慢查詢sql的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2021-12-12
這篇文章主要介紹了Linux中查看MySQL服務(wù)是否開啟方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-09-09
MySQL查詢?nèi)繑?shù)據(jù)集結(jié)果不一致問題解決方案
最近出現(xiàn)一個很奇怪的MySQL問題，使用不同select語句查詢?nèi)繑?shù)據(jù)集居然得到不同的記錄數(shù)2012-11-11
下面小編就為大家?guī)硪黄狹ysql GTID Mha配置方法。小編覺的挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
使用MySQL 數(shù)據(jù)庫的站點(diǎn),當(dāng)訪問連接數(shù)過多時,就會出現(xiàn) "Too many connections" 的錯誤,所以我們需要設(shè)置MySQL查看和修改最大連接數(shù),具有一定的參考價值,感興趣的可以了解一下2023-10-10
Mysql學(xué)習(xí)之?dāng)?shù)據(jù)庫檢索語句DQL大全小白篇
這篇文章主要介紹了Mysql數(shù)據(jù)庫檢索語句DQL大全，本文適合數(shù)據(jù)庫初學(xué)者，小白也能看懂，有需要的朋友可以收藏閱讀，希望可以有所幫助2021-09-09
Mysql數(shù)據(jù)庫中數(shù)據(jù)表的優(yōu)化、外鍵與三范式用法實(shí)例分析
這篇文章主要介紹了Mysql數(shù)據(jù)庫中數(shù)據(jù)表的優(yōu)化、外鍵與三范式用法,結(jié)合實(shí)例形式較為詳細(xì)的分析了Mysql數(shù)據(jù)庫中數(shù)據(jù)表的優(yōu)化、外鍵與三范式相關(guān)概念、原理、用法及操作注意事項,需要的朋友可以參考下2019-11-11
這篇文章主要介紹了MySQL?備份之?into?outfile，文章圍繞主題展開詳細(xì)內(nèi)容介紹，具有一定的參考價值需要的小伙伴可以參考一下2022-05-05