Solved FreeBSD 13.0-RELEASE (MariaDB 10.5) - How to Rebuild an old INNODB on a New Installation of MariaDB Server?

StreetDancer

StreetDancer

Hey everyone!

I had a major failure doing things incorrectly. My woes are documented in detail here: https://forums.freebsd.org/threads/..._packet_size-variable-help.85015/#post-565507

I took the great advice of richardtoohey2 (Who I consider a friend here on FreeBSD Forums) and created a New Maria DB 10.5 Jail.

I installed everything. I then copied all /var/db/mysql (from Crashed MariaDB 10.5 Server Jail) to the brand new Maria DB 10.5 Jail that's working and currently running.

This is how things are setup now:

datadir paths:

/var/db/mysql (Contains the new Fresh installation of Maria DB 10.5 that works)
/var/db/mysql0 (Contains the Old Maria DB 10.5 with all my databases and files; which contains all my tables inside each folder perfectly *.ibd's, *.frm's, etc.)

I updated /usr/local/etc/mysql/conf.d/server.cnf and changed the following line:

Code: 
datadir = /var/db/mysql

to:

Code: 
datadir = /var/db/mysql0

then issued the following to restart MariaDB:

Code: 
# service mysql-server restart

All works fine regarding the server, still. With this output:

Code: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.15-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.003 sec)

root@localhost [(none)]>

I have over 100+ databases that I need on this new installation. What are the steps for restoring them properly?

Do I have to re-create each database on the new one and then manually copy over the contents of each folder?

Is there anyway to preserve all Permissions, and Previous Schema and load it into the new one?

Update - 05/02/2022 @ Approx. 17:23:

1) I edited /usr/local/etc/mysql/conf.d/server.cnf and changed the datadir = /var/db/mysql0 back to datadir = /var/db/mysql
2) mv /var/db/mysql /var/db/mysql_disabled
3) mv /var/db/mysql0 /var/db/mysql
4) chown -R mysql:mysql /var/db/mysql
5) service mysql-server restart

Then attempted a mysql -u root -p and received a password error, hopefully indicating that I restored my old MariaDB 10.5 Server and it truly wants my Credentials from the Crashed MariaDB 10.5 Server.

I will report my findings once I login with other Credentials! Thank you all!


Best Regards,

Brandon!
 
If you try

Code: 
show variables like '%dir%';

at the MariaDB command prompt, does it look like it's pointing at the right directory?

I'm still hoping someone shows up who can help with the original problem!
 
If I got, you should read about the jails. In my knowledge, the jails "jail" a process, or a couple. Maybe you should take a look about it. I'm so sorry, but my known, gives shame. Despite of it, I've try to use those, so, I have a barely idea of whats the problem.
Did you passed the processes to an specific jail, or did you create in these? Such process, are connected to another jail/s?
Again, I make apologizes. But it's that all I've learned and practiced. Sorry.
 
richardtoohey2 said:
If you try

Code: 
show variables like '%dir%';

at the MariaDB command prompt, does it look like it's pointing at the right directory?

I'm still hoping someone shows up who can help with the original problem!
Click to expand...
richardtoohey2,

I just updated my last post and didn't see your post here. I got it restarted using the old database folder called "/var/db/mysql" ; rather than /var/db/mysql0 (Changing the dirpath in /usr/local/etc/mysql/conf.d/server.cnf doesn't work right for some reason).

After that I chown -R mysql:mysql /var/db/mysql and # service mysql-server restart and now it wants my old root password.

It *could* be fixed.

I haven't gotten further yet!

Best Regards,

Brandon
 
n0.0 said:
If I got, you should read about the jails. In my knowledge, the jails "jail" a process, or a couple. Maybe you should take a look about it. I'm so sorry, but my known, gives shame. Despite of it, I've try to use those, so, I have a barely idea of whats the problem.
Did you passed the processes to an specific jail, or did you create in these? Such process, are connected to another jail/s?
Again, I make apologizes. But it's that all I've learned and practiced. Sorry.
Click to expand...
n0.0,

No worries. I created MariaDB 10.5 Server from within the Jail. Jails are very cool and I encourage you learn "ezjail-admin" which allows rapid deployment, backups and restores of Jails. Very easy to use and incredibly intuitive.

Best Regards,

Brandon
 
StreetDancer Well, I just tried using those on Tor, the browser. No funny what you can see there. Obviously, since I am a very sensible person, I didn't look for it to make "rare" things. Just curiosity. Once I saw the cr4p there, I quit. And forget. Now, the jails, are very useful, apart of it, just to confine processes... That I suspect they didn't report good things to my OS.
Now, with my pathetic connection, I have to restrict the use of the GB.
Well, they weren't outside. But such processes, are connected to another jails, or the system?
 
richardtoohey2 said:
You keep using this file but I’m not sure why?

I think the default is my.cnf but I might be behind the times.

/usr/local/etc/mysql/my.cnf

But maybe something to do with MariaDB?

Does that server.cnf exist after a clean install or are you creating it?
Click to expand...
richardtoohey2,

You are correct; default is /usr/local/etc/mysql/my.cnf ;however, inside contains an !include /conf.d/* line which Includes conf.d/server.cnf (as combined set of variables).

And after a clean install; yes it exists and is required to start MariaDB 10.5.x

Best Regards,

Brandon
 
Alright; I didn't solve the original mysql-server / MariaDB Server 10.5.x error;

However I was able to do as richardtoohey2 suggested and it worked great!

Once I copied the /var/db/mysql from Crashed Server to duplicate MariaDB Server 10.5.x (New Jail) and attempted to change the data dir path from my.cnf (which inherits conf.d/* , server.cnf); doing this alone did not work.

I had to disable the stock version (from new installation) /var/db/mysql to /var/db/mysql_disabled and then I did mv /var/db/mysql0 to /var/db/mysql

Then chown -R mysql:mysql /var/db/mysql

At this point; the new MariaDB 10.5.x Server Jail did not match IP settings for ezjail-admin that the old one did.

So I turned the jail off; and ran "ezjail-admin archive NewMariaDBJail" ; offloaded a copy for backup and then deleted it.

I then re-created "NewMariaDBJail" using the archived NewMariaDBJail ezjail-admin Jail; only this time, using the old server's IP settings.

Brought up the New Jail; edited the /usr/local/etc/mysql/conf.d/server.cnf and updated the IP settings and created a new logfile name to keep all things separate and new.

Restarted the Jail and Viola!

title_g5-thelegendofzelda-450x191.gif

Image Source: Picture Source

Code: 
root@Jail:~ # mysql -h LOCALIP -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.5.15-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@NewMariaDBJail [(none)]> show databases;


OUTPUT:

ALL 539 Databases: (ommitted),

| mysql                                |
| performance_schema                   |
| phpmyadmin                           |    |
+--------------------------------------+
539 rows in set (0.092 sec)

root@NewMariaDBJail [(none)]>


Thank you richardtoohey2 and thumbs from #irc.libera.chat #mysql for verifying my strategy before I attempted it. He also suggested to chown and I wouldn't have done that since it already showed "mysql" as the user; however, now I believe it processed to the "current jail/installation"

SOLVED! Out of the Woods! :)
 
You must log in or register to reply here.
Back
Top