Preparing container

Let's start with a connection:

mysql -h0 -P9306

We need some content in the table to have something to back up.

Creating a few tables for test

Let's create a table with some fields: info field of type text and a numeric value:

create table demo (info text, value integer);

For the test, let's add a few entries:

insert into demo (info, value) values ('Demo string 1 [ART]', 1), ('Demo string 2 [RET]', 2), ('Demo string 3 [PET]', 3);

Let's see the current table parameters:

desc demo;

And its contents:

select * from demo;

Let's now exit Manticore to run mysqldump in the terminal:

exit;

Create full database dump

Let's make a snapshot of the entire database and save it to a file named 'manticore.sql'.

mysqldump --compact -h0 -P9306 manticore > manticore.sql

Apply this dump to new empty instance

Now let's remove everything in the database:

Let's connect:

mysql -h0 -P9306;

Delete the current table:

drop table demo;

Checking if there is anything left:

show tables;

The database is empty, now let's restore the data back from the newly created dump.

exit;

To restore, just pass a file with a database image to the mysql client input:

mysql -h0 -P9306 < manticore.sql

Now you can log in and check what we ended up with:

mysql -h0 -P9306

Let's look at the tables:

show tables;

Let's see what's inside the table:

select * from demo;

All data is there and the recovery was successful.

exit;

Mysqldump for full-text reindexation

For demonstration, let's add a word forms file to the table. Let's create it:

echo "ART > RET" > /tmp/wordforms.txt

Now we need to connect it to the table. Let's log in:

mysql -h0 -P9306

And add the file:

alter table demo wordforms = '/tmp/wordforms.txt';

Now let's check whether the file is connected in the table settings:

show table demo settings;

Let's check how the search works:

select * from demo where match('ART');

At the moment, only one record is displayed, although the word forms file is connected. This is due to the fact that the old entries were made before the file was connected and therefore it has not yet participated in the indexing of these entries. In order to update these records, the 'REPLACE' command is used and to automate this process we'll use a modified query for mysqldump:

Let's log out of Manticore:

exit;

and run mysqldump with the following configuration:

• --compact will remove all unnecessary locking and table preparation commands
• --replace will make mysqldump generate 'REPLACE' instead of 'INSERT' in the dump, which will let us do what we want
• -e will enable bulk write queries
• -t will disable table creation
• -c will add field names to the replace queries. This is useful not only when changing a full-text setting but also when updating the schema. We'll direct the output of the mysqldump command to the mysql client, skipping the step of saving the dump to a file, to simplify the process.

mysqldump --compact --replace -etc -h0 -P9306 manticore demo | mysql -h0 -P9306

Reindexing is now complete. Let's check what we have now:

Let's connect to the mysql client:

mysql -h0 -P9306

And now let's check how our 'select' query works:

select * from demo where match ('ART');

Great! The added word form is now effective.

exit;