Changing Mysql 5.5 to MariaDB 10.2

Richard G

Richard G

Verified User
Joined
Jul 6, 2008
Messages
13,707
Location
Maastricht
I would like to try and change to MariaDB 10.2
Now this is my current my.cnf completely:
Code: 
[mysqld]
local-infile=0
bind-address = 127.0.0.1
default-storage-engine=MyISAM
innodb_file_format=Barracuda
innodb_file_per_table=1
open_files_limit = 2048
innodb_max_dirty_pages_pct = 0
performance_schema=off
slow_query_log = 1
slow_query_log_file=/var/log/mysql/slow-queries.log
long_query_time = 10

# query_cache_size = 16M
# query_cache_type = 1

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

Can this be kept or is it best to change some things of it?

Next I have to convert to MariaDB 10.2 but I found 2 ways of doing this.

Method 1:
Code: 
cd /usr/local/directadmin/custombuild
./build update
./build set mariadb 10.0
./build set mysql_inst mariadb
./build mysql

Method 2:
Code: 
1. Create a copy/backup of /var/lib/mysql
2. Edit /usr/local/directadmin/custombuild/options.conf and set the mariadb version you want to use.
3. Run /usr/local/directadmin/custombuild/build mariadb
4. You're done!

The major difference in both methods is that in method 1 ./build mysql is used and in method 2 ./build mariadb is used.
I presume the second one is correct?

And ofcourse creating a backup of /var/lib/mysql is also a good idea so I will do this anyway.

Any other thoughts, changes, improvements or anything else I have to take into consideration?
 
I went from Mysql 5.6 to MariaDB 10.1 (after doing a full server backup/snapshot first).
Maria 10.2 was available, but I was afraid a too big upgrade-step might wreck things.

The update to php7 was noticable on my server when certain wordpress-sites loaded slightly quicker.
I'm not sure yet I've 'really' noticed a speed-update after going to MariaDB, I think there's a tiny improvement, but I would need to do speedtests to really confirm.

I also made the mysql-directory backup first, changed options.conf and did Build, although I can't remember doing "Build mysql" or "Build mariadb".
I *think* I did "Build mysql".
But would it matter?

I didn't change anything in my.cnf.
This is the contents of my my.cnf, which I arrived at during occassional trial-and-error testing with "./mysqltuner.pl" and also trying not to exceed the vps's available (4GB) memory;
Code: 
[mysqld]
bind-address = 127.0.0.1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
local-infile=0

# Skip reverse DNS lookup of clients
skip-name-resolve = 1

slow_query_log = OFF
slow_query_log_file=/var/log/mysqlslow.log
long_query_time = 10

log_queries_not_using_indexes=1

query_cache_type = 1
query_cache_limit = 15M
query_cache_min_res_unit = 1500
query_cache_size = 300M
thread_cache_size = 4
max_connections = 50

innodb_buffer_pool_size = 380M
innodb_buffer_pool_instances = 1

tmp_table_size = 250M
max_heap_table_size = 250M
join_buffer_size = 35M
key_buffer_size = 20M
table_open_cache = 15000
open_files_limit = 15000

interactive_timeout=150
wait_timeout=150

[mysqld_safe]
log-error=/var/log/mysqld.log
 
As far as I know "./build mariadb" is just an alias for "./build mysql". It just depends on the mysql_inst setting. So if you have mysql_inst set to mysql you can use ./build mariadb or ./build mysql and vice versa for mysql_insta = mariadb.

And be sure to have backups of everything! If you can make a snapshot of your system definitely make one. I learned this the hard way :eek:
 
Last edited:
Thank you both for your replies.

The update to php7 was noticable on my server when certain wordpress-sites loaded slightly quicker.
Click to expand...
Is that needed? I would like to keep the server running on 5.6.x for the time being.

Good to know that the build is only an alias, makes things easier.

I'm just looking for the best way to backup. It's a dedicated server so I can't do a snapsnot like a VPS can.
So I'm wondering if I make a copy of the /var/lib/mysql directory and then to the conversion, if things go wrong, can't I just revert to mysql 5.5.x and then copy the content of that backup back to /var/lib/mysql? Or is that thought too easily?

And I'm still wondering about these 3 lines in the my.cnf if they need change:
Code: 
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_max_dirty_pages_pct = 0
 
The upgrade to php7 can best be done seperatly, as some, for instance older or incompatible Wordpress plugins can render white pages on certain sites.
Also had to change phpbb-forums config files to use a mysqli-setting instead of mysql.


I think if you remove the lines in my.cnf, the settings will return to their default values.

This is what mysqltuner.pl reports about my settings;
Code: 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 380.0M/89.4M
[OK] InnoDB log file size / InnoDB Buffer pool size: 48.0M * 2/380.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.92% (5610249 hits/ 5614772 total)
[!!] InnoDB Write Log efficiency: 63.34% (38943 hits/ 61486 total)
[OK] InnoDB log waits: 0.00% (0 waits / 22543 writes)
 
The upgrade to php7 can best be done seperatly
Click to expand...
Thank you, but my question was if php 7 was needed or required for MariaDB 10.x because I rather want to stay at PHP 5.6 for some time.
 
Thank you all!

Oeps. Just noticed that one was not confirmed.
Is backing up the /var/lib/mysql directory enough as backup?
 
Oke, what is the best way to do this, I presume with mysqldump. Like this?

Code: 
mysqldump -u da_admin -h localhost -p --all-databases > all_databases.sql

Or is there a better way?
 
Regarding database backup just before upgrade of MariaDB. Why don't you use the option in custombuild to do this? It's in options.conf with these two lines:

Code: 
mysql_backup=yes
mysql_backup_dir=/usr/local/directadmin/custombuild/mysql_backups
 
Thank you Ditto.
I do have these lines set like that on all servers for when I do mysql upgrades. I wasn't sure if that would also work with a conversion to MariaDB because in other threads it was advised to do a backup first.
I'll just make a second backup then just to be sure.
Also with the tool Sellerone suggested, it's possible to automatically make daily, weekly and/or monthly backups automatically.
 
Richard G,

Why such low open_files_limit?

Also it does not look like you tuned up anything regarding buffers. Depending on the amount on ram on the server, you can achieve a good performance increase if you tune-up things a bit.
 
@Wattie:
I was like 1024 by default so I just doubled it.
I only do maintenance and support on 3 servers. I tried mysqltuner and changed some things before but then things got messed up so I put it back to default values. I'm not a mysql guru so I rather say of things I don't know about.
We got 1 server with 16 GB RAM and 2 servers with 32 GB of ram.
The one is the 16 GB is the main server, we had that one first.
 
So on this 1 server I got these results, No clue on what it means. :)
Code: 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
 
To get a very rough estimate of how many RAM your MySQL/MariaDB installation is using MAX (when your server get max load, which means max_connections), run the following query as da_admin:

Code: 
SELECT ( @@key_buffer_size + @@query_cache_size + 
         @@tmp_table_size + @@innodb_buffer_pool_size + 
         @@innodb_additional_mem_pool_size + 
         @@innodb_log_buffer_size + 
         @@max_connections * ( 
             @@read_buffer_size + @@read_rnd_buffer_size + 
             @@sort_buffer_size + @@join_buffer_size + 
             @@binlog_cache_size + @@thread_stack 
         ) 
) / 1073741824 AS MAX_MEMORY_GB

Next, if you think you can give much more, look at the description of the variables in the query and decide which you can increase more. There are much more variables available of course but these are the most important for tuning.
 
Last edited:
Not quite sure if this is related to the MariaDB upgrade, but Yum is showing a warning recently:

Code: 
yum version
Loaded plugins: fastestmirror, presto, security
Warning: RPMDB altered outside of yum.
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
galera-25.3.20-1.rhel6.el6.x86_64 has missing requires of libboost_program_options.so.5()(64bit)

Tried "rpm --rebuilddb" but this didn't help.
 
You must log in or register to reply here.
Back
Top