转移 MySQL 的数据（datadir） - Jacky Liu's Blog

转移 MySQL 的数据（datadir）

Jacky Liu posted @ 2010年9月27日 09:11 in MySQL with tags mysql datadir 转移数据 , 5135 阅读

---- 开始看 MySQL 不久。还没做任何实际操作之前，就想把它默认的存储数据库文件的目录从 "/var/lib/mysql" 搬到 "/home" 自己的目录下面。一般来说，需要转移 MySQL 数据目录的原因可以有很多，比较典型的是因为原来的硬盘不够大。不过对我来讲没那么复杂，主要是因为想把关键文件都放一起，这样下次系统崩溃的时候比较容易把文件倒出来。

---- 这个并不复杂的问题差不多折腾了一整天。其实关键就下面几点：

    1. 找到 mysql 启动时加载的 Option File（如果有多个，那么一定是起作用的那一个）。在我的机器上只有一个，是 "/etc/mysql/my.cnf"，进去把 datadir 一项的值改成自己指定的目录。这个不难。

    2. 最关键的，把原先的数据目录（默认是 "/var/lib/mysql"）里面的东西都转移到新的目录下，而且！一定！要保证相关的内容在新的目录下仍然保持跟原来一样的用户和权限设置。用 chown 和 chmod 就能搞定，很简单。但是如果这步出了差错，找起来就麻烦了，重新启动 mysql 的时候只会说连不上，很难发现真正的原因。我是通过 Ubuntu 下的 Package Manager 安装的 MySQL，安装之后相关文件和目录的 user name 和 group name 都是 “mysql”，所以新的 datadir 也要将 owner 设成 mysql，并确保它有合适的权限。

    3. 不仅要保证 datadir 的用户/权限设置正确，如果 datadir 目录比较深的话，好像跟上面几级目录的权限也有关系。总之，要保证用户 “mysql” 能顺利地访问新的 datadir 的内容，而且要保证所有必要的内容都已被复制到新的 datadir 下面。在 MySQL 里面，像用户记录这种启动时必需的信息也是以数据库形式保存的，mysql 在启动时会加载这些必需的 database，如果相关目录的权限有问题，或者文件根本不存在，都会启动失败。

    4. 关于 AppArmor，网上资料说需要把所做的改动通过 profile 文件告诉它，否则 AppArmor 会阻止 mysql 访问新的文件路径而导致 mysql 不能运行。以我的经验来看，好像没感觉到 AppArmor 的影响。不过保险起见，还是按照网上说的，修改一下它的 profile 文件，在我的机器上是： "/etc/apparmor.d/usr.sbin.mysqld"，照着原先 datadir 有关内容的格式，新加几行：

        /my/new/datadir/ r,
        /my/new/datadir/** rwk,

    5. AppArmor 和 mysql 都需要重启。重启的命令是：

        sudo /etc/init.d/{apparmor|mysql} stop
        sudo /etc/init.d/{apparmor|mysql} start

    或者用 service 命令也可以：

        sudo service {apparmor|mysql} stop
        sudo service {apparmor|mysql} start

---- TroubleShooting:

    mysql 的 Error log 文件，在我的机器上是： "/var/log/mysql/error.log"，如果出了问题，这里记录的是真正的原因。对于终端里的几行输出不用太在意，基本上无论出什么事都只会说连不上 Server，没什么用。

全局相关文章
相关文章

[回复]

ludo king 说:
2018年7月27日 17:23

I enjoyed over read your blog post. This was actually what i was looking for and i am glad to came here!

[回复]

Uttarakhand Board Qu 说:
2022年9月16日 19:14

Uttarakhand Board of School Education (UBSE) have introduced Sample Paper suggestions for all High School Level Class 6th Standard Students to know the new exam scheme or question pattern of Term1 & Term2 exams. Uttarakhand Board Question Paper Class 6 Various private originations teaching staff and others have designed and suggested UK Board 6th Class Model Paper 2023 with Mock Test and Practice Questions for Term1 & Term 2 Exams of the Course. Advised to download and practice the learning & study materials for all Languages and Subjects of the Course for SCERT & NCERT Syllabus to score top marks in every exam.

[回复]

Emma 说:
2023年1月24日 22:16

Moving data to a new MySQL directory can be a challenging task, but with the right steps it can be done quickly and efficiently. This comment provides a guide to transferring MySQL data to a new datadir, ensuring that the process is as smooth and peter veres cancer help hassle-free as possible. By following these instructions, users can ensure their data is moved safely and securely, and they can begin using their new MySQL datadir immediately.

[回复]

बोर्ड-हिंदी-प्रश्नपत 说:
2023年5月19日 19:43

Is a project of experienced writers who have gathered for specialised news coverage of current events across the nation (India). Our team is made up of professional writers and citizen journalists with a wide range बोर्ड-हिंदी-प्रश्नपत्र.com of journalism interests who are committed about delivering education updates in the public interest while maintaining transparency. Our reporting team plans to release the Education & Recruitment Update for all age groups and provide

[回复]

anonymous 说:
2023年9月13日 18:52

Cool you write, the information is very good and interesting, I'll give you a link to my site. Deltona Exterminator

[回复]

anonymous 说:
2023年10月01日 20:11

Thanks for writing such a good article, I stumbled onto your blog and read a few post. I like your style of writing... Home Renovations Orlando

[回复]

anonymous 说:
2023年12月04日 01:06

In this particular article, you will see a summary, satisfy browse this post. CPA Virtual Services

[回复]

anonymous 说:
2023年12月06日 19:29

I can give you the address Here you will learn how to do it correctly. Read and write something good. hillock green showroom

[回复]

anonymous 说:
2023年12月22日 19:33

On this page you can read my interests, write something special. www.fnf-mods.org

[回复]

anonymous 说:
2024年1月02日 19:44

<p>
<span style="font-family: Verdana, Geneva, sans-serif; font-size: 12px;">Thanks for writing such a good article, I stumbled onto your blog and read a few post. I like your style of writing... </span><a href="https://www.amazon.com/Months-Inside-Prison-Newborns-Halloween/dp/B084WW13FK/" style="color: rgb(0, 0, 170); font-family: Verdana, Geneva, sans-serif; font-size: 12px;">newborn baby onesie gift</a></p>

[回复]

anonymous 说:
2024年1月02日 19:47

Thanks for writing such a good article, I stumbled onto your blog and read a few post. I like your style of writing... newborn baby onesie gift

[回复]

anonymous 说:
2024年1月21日 16:23

Pay Per Return Tax Software - Whether you just want to know what features are out there, or you have years of experience and are just looking for a handy tax software comparison tool to help you maximize profits this season, you’ve come to the right place. We’re here to help you choose the best tax software pricing and features without going through the hassle of intense research. We’ve done the hard part for you.

[回复]

Victoria Willis 说:
2024年1月28日 21:22

I am always searching online for storys that can accommodate me. There is obviously a multiple to understand about this. I feel you made few salubrious points in Attributes moreover. Detain busy, awesome career! Unique Tiles

[回复]

Clay Lowe 说:
2024年2月08日 22:04

EagleCreekNursery - At our Gardening Nurseries and landscape center, you can enjoy an extensive selection of plants, trees and shrubs in stock that we have selected for their beauty, variety, and survivability. Find out Nurseries for Trees near me and Landscape Nursery near me.

[回复]

Clay Lowe 说:
2024年3月06日 15:11

At Southern Outdoor Design, we understand the importance of working from a landscape design when bringing an outdoor transformation to life. Our team of industry all-stars delivers beautiful 2D, 3D, and hand-crafted Landscape Design Atlanta.

[回复]

Clay Lowe 说:
2024年3月18日 12:36

For many people this is important, so check out my profile: superace88

[回复]

Clay Lowe 说:
2024年3月19日 00:11

It is rather very good, nevertheless glance at the data with this handle. barcelona chairs

[回复]

Clay Lowe 说:
2024年3月20日 12:39

Element212 aim to be your trusted Web Designers Indianapolis. We support family businesses in every aspect of their brand, from developing engaging websites to search engine optimization.

[回复]

Clay Lowe 说:
2024年3月23日 19:28

I invite you to the page where you can read with interesting information on similar topics. eames lounge chair and ottoman replica

[回复]

Clay Lowe 说:
2024年3月26日 00:03

The most interesting text on this interesting topic that can be found on the net ... Commercial Security

[回复]

Clay Lowe 说:
2024年3月29日 13:46

Your blogs further more each else volume is so entertaining further serviceable It appoints me befall retreat encore. I will instantly grab your rss feed to stay informed of any updates. NBA即時比分

[回复]

Clay Lowe 说:
2024年4月09日 00:33

I have a similar interest this is my page read everything carefully and let me know what you think. taxi from Southampton to London