Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: How to install and configure MariaDB on Ubuntu Server 18.04

  1. #11
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,056
    Distro
    Ubuntu 16.04 Xenial Xerus

    Re: How to install and configure MariaDB on Ubuntu Server 18.04

    Migrate Database from Old Server

    You can use the full backup automatically made by the "db-backup.sh" script (/bak/mysql/db-all.sql) if you have that already running or you can create a manual backup. The steps below will document how to do it manually but you could just as easily use your existing backup file(s).

    Here are the steps to import the databases from the old server into the new server:


    1. On the old server, make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all.sql
    2. Transfer db-all.sql to the new server (via WinSCP or other means)
    3. On the new server, make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all-before.sql
    4. Import the db-all.sql file by typing: mysql < /tmp/db-all.sql
    5. Make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all-after.sql
    6. Verify that your databases, tables, rows and users are now on the new server
    7. Archive the /tmp/*.sql files for safe-keeping until you no longer need them. If you leave them in /tmp, they will be automatically deleted upon next reboot.
    8. Now would be a good time to run your backup script: /var/scripts/prod/db-backup.sh


    Now you can point your apps to the new database server or shutdown the old server and then change the IP of this server to match the old server.

    NOTE: Saving the following lines in case I expand this further by showing how to extract an archive 1st and then restore from that.

    • Make a restore folder by typing: mkdir /tmp/restore
    • Extract the archive by typing: 7za x -so -w/tmp /tmp/*.7z | tar -C /tmp/restore --strip-components=3 -xf -



    NOTE: If you see the following error in the syslog, especially after doing an "apt-get upgrade" and noticing the mysqld service stopped, you need to simply run "mysql_upgrade" to fix the problem. Reference Bug

    Code:
    Jun  1 14:25:48 srv-mysql mysqld_safe[23145]: ERROR: 1136  Column count doesn't match value count at row 1
    Jun  1 14:25:48 srv-mysql mysqld_safe[23145]: 2017-06-01 14:25:48 140594627692800 [ERROR] Aborting
    Jun  1 14:25:50 srv-mysql mysqld_safe[23145]: Installation of system tables failed!
    Migrating Individual Databases from Old Server

    If you export one database at a time, it will not include the grants. You will need to export the grants and then import the database, then import the grants on the new server.

    Example: Let us assume the database we want to migrate is called "mydb"

    On the old server:
    Code:
    mysqldump --databases mydb > /tmp/mydb.sql
    mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g' > /tmp/mydb-grants.sql
    Now edit the mydb-grants.sql file and delete everything not related to the DB you want.

    Copy both .sql files to the new server and then execute the following commands:

    Code:
    mysql < /tmp/mydb.sql
    mysql < /tmp/mydb-grants.sql
    Restoring a database without generating a log

    If you are restoring/migrating a huge database and do not want a log generated for the import, you can temporarily disable logging for your session as follows:

    Code:
    mysql
    SET sql_log_bin = 0;
    source /tmp/mydb.sql
    exit

  2. #12
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,056
    Distro
    Ubuntu 16.04 Xenial Xerus

    Re: How to install and configure MariaDB on Ubuntu Server 18.04

    Crontab Schedule

    I would not advise anyone to ever "edit" a live crontab schedule by typing "crontab -e" but rather edit a saved schedule file and then load the schedule file. This will allow you to make backups of the schedule so you can always go back to a known-good schedule or at least back to the way it was before you made a change...assuming you always work with a copy of the schedule 1st.

    Here is my root crontab scheduling file:

    /var/scripts/data/crontab.root
    Code:
    ########################################
    # Name: Crontab Schedule for root user
    # Author: LHammonds
    ############# Update Log ###############
    # 2012-05-20 - LTH - Created schedule
    ########################################
     
    SHELL=/bin/sh
    PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
     
    # m h dom mon dow command
     
    #
    # Backup Databases
    #
    0 23 * * * /var/scripts/prod/db-backup.sh > /dev/null 2>&1
    #
    # Database On Demand Backup
    #
    0-59 * * * * /var/scripts/prod/db-ondemand-backup.sh > /dev/null 2>&1
    #
    # Daily check for available space on /var
    #
    0 1 * * * /var/scripts/prod/check-storage.sh opt 50 50 > /dev/null 2>&1
    #
    # Daily check for available space on /backup
    #
    0 2 * * * /var/scripts/prod/check-storage.sh bak 50 50 > /dev/null 2>&1
    #
    # Daily check for available space on /temp
    #
    0 3 * * * /var/scripts/prod/check-storage.sh tmp 50 50 > /dev/null 2>&1
    Once you have created the file, make sure appropriate permissions are set by typing the following:
    Code:
    chown root:root /var/scripts/data/crontab.root
    chmod 0600 /var/scripts/data/crontab.root
    To enable the root schedule using this file, type the following:
    Code:
    crontab -u root /var/scripts/data/crontab.root
    To disable the root schedule, type the following:
    Code:
    touch /tmp/deleteme
    crontab -u root /tmp/deleteme
    rm /tmp/deleteme
    If you need to modify the schedule, make a backup copy 1st. For example:
    Code:
    cp /var/scripts/data/crontab.root /var/scripts/data/2011-11-28-crontab.root
    vi /var/scripts/data/crontab.root (make your changes)
    crontab -u root /var/scripts/data/crontab.root

  3. #13
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,056
    Distro
    Ubuntu 16.04 Xenial Xerus

    Re: How to install and configure MariaDB on Ubuntu Server 18.04

    Fail2Ban

    If you have Fail2Ban installed and protecting SSH as part of the base install. You can add these few changes to also watch for database login failures.

    Code:
    vi /etc/fail2ban/jail.local
    Add the following to the bottom. If your data path is different, be sure to update "logpath"

    Code:
    [DEFAULT]
    ## 127.0.0.1/8 = ignore login failures on the local machine
    ## 192.168.107.0/24 = ignore login failures on an entire subnet
    ## 192.168.107.55 = ignore login failures for this specific IP
    ignoreip = 127.0.0.1/8 192.168.107.55
    
    ## "bantime" is the number of seconds that a host is banned.
    ##  300 =  5 minutes
    ##  600 = 10 minutes
    ##  900 = 15 minutes
    ## 1800 = 30 minutes
    ## 3600 = 60 minutes
    bantime = 1800
    ## "findtime" is the length of time between login attempts before a ban is set.
    findtime = 300
    ## "maxretry" is how many attempts can be made to access the server from a single IP before a ban is imposed.
    maxretry = 5
    ## "destemail" is the email address where you would like to receive the emails.
    destemail = lhammonds@mydomain.com
    ## "sender" is the FROM: address when it arrives in your mailbox.
    sender = ubuntu@mydomain.com
    ## Use the lightweight sendemail instead of sendmail
    mta = sendemail
    ## Email notify with whois report and relevant log lines when a ban occurs
    action = %(action_mwl)s
    
    [mysqld-auth]
    enabled = true
    filter  = mysqld-auth
    port    = 3306
    logpath = /var/log/mysql/error.log

  4. #14
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,056
    Distro
    Ubuntu 16.04 Xenial Xerus

    Re: How to install and configure MariaDB on Ubuntu Server 18.04

    Web Front-end

    NOTE: I have not tested this on 18.04 yet

    Someone asked if there was a graphical way to manage the server. After a long sigh, I mentioned that phpmyadmin is a popular front-end.

    Here is how you install it:
    Code:
    apt install phpmyadmin
    This will install Apache web server along with PHP and various libraries. I would recommend uninstalling it after your initial setup since having an Apache web service tends to eat away at your CPU/RAM. My corporate DB server runs happily on 512 MB of RAM. It is also a greater security risk with PHPMyAdmin installed due to additional services which can be attacked for vulnerabilities.

    Code:
    apt remove phpmyadmin

  5. #15
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,056
    Distro
    Ubuntu 16.04 Xenial Xerus

    Re: How to install and configure MariaDB on Ubuntu Server 18.04

    Rename root

    For increased security, you can rename the root account to something else with the following commands:
    Code:
    USE mysql;
    UPDATE USER SET User='NewSecretRoot' WHERE User='root';
    FLUSH PRIVILEGES;
    Review User Accounts

    Code:
    SELECT User,Host,Password FROM mysql.user;

    1. Make sure everyone has a password set.
    2. If there are any hosts as "%" then review to make sure it is absolutely necessary. If possible, use "localhost" first, then use the specific hostname/IP after that. The "%" wildcard should be a last resort for special cases like the app server name/IP changes all the time or there are multiple app servers connecting using the same ID.



    You can check the permissions of each login to make sure they have the least permissions necessary to get the job done:
    Code:
    show grants for 'someuser'@'localhost';
    If said user has DELETE privilege when it is not needed, you could remove it using a command like the following:
    Code:
    REVOKE DELETE ON appdatabase.* FROM 'someuser'@'localhost';

  6. #16
    Join Date
    Jul 2018
    Beans
    1

    Re: How to install and configure MariaDB on Ubuntu Server 18.04

    thank you very much

  7. #17
    Join Date
    Sep 2011
    Location
    Behind you!
    Beans
    1,056
    Distro
    Ubuntu 16.04 Xenial Xerus

    Re: How to install and configure MariaDB on Ubuntu Server 18.04

    FYI - My HammondsLegacy.com site might go offline for a while due to financial issues but it will return once things become stable again.

    EDIT 2018-12-05: My company closed down and my site will go offline soon. Once things become stable, I will get the site back up and all the links should start working again.
    Last edited by LHammonds; December 5th, 2018 at 09:43 PM.

Page 2 of 2 FirstFirst 12

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •