0

I have the following case, but no possibility to make a test. Would be thankful for the advice.

A) local host with the MySQL server (an old mysql 5.0)

A.1) This MySQL server has a few databases

A.2) One of these databases contains some innoDB tables, but also a few "ndbcluster" (ENGINE=ndbcluster) ones.

B) remote NDB cluster with a huge set of data used on the local host in A)

My goal is to shrink the DB (remove tabledata files) on the local host. For this, I will have to drop all the databases and then remove the physical DB files. Then, add innodb_file_per_table to my.cnf and start MySQL server.

The questions:

1) AFAIK when I execute DROP DATABASE db_name, it first removes all the tables inside the DB. How can I ensure that when I drop the database, the tables/data inside the remote NDB cluster is not affected (it is used by other similar local servers)?

2) When I insert the dump back (it will recreate the dropped database) to the refreshed local MySQL server, will it add/connect the ndbcluster tables from the NDB cluster automatically?

local my.cnf has the following lines: ndbcluster ndb-connectstring="nodeid=35,1.2.64.69,1.2.64.70"

3) Do I really need to drop all the database to remove the physical files and recreate the DB in the folder per table mode?

Yaroslav Fedorina
  • 36
  • 6

2 Answers2

0

DROP DATABASE will remove all tables in the database, both InnoDB and NDB. So you will have to remove table by table if you want to keep the NDB tables.

Not sure of the answers relating to InnoDB.

Mikael Ronström
  • 404
  • 2
  • 3
0

To ensure that I am not touching the NDB tables, I skipped removing the virtual databases. So, the procedure briefly looked like this:

  • Dump the mysql db and other important DBs
  • Stop the MySQL service

  • Move/remove the db files:

    cd /var/lib/mysql/ && rm -f ib_logfile0 ib_logfile1 tablespace1 tablespace2

  • Re-initialize mysql and add the root user (as it would not start normally):

    cd /var/lib/mysql && mysql_install_db

  • Start the mysql server
  • Insert the mysql db (from dump) using the default root user without a password.
  • Insert other databases using the old (restored) user.

When the database that includes an NDB table is inserted, the NDB table will be connected automatically.

Yaroslav Fedorina
  • 36
  • 6