Resource Setting Up mySQL On Ubuntu 16.04!

Discussion in 'Spigot Plugin Development' started by MGPro, May 21, 2017.


Did this thread help you?

  1. Yes

  2. No

Results are only viewable after voting.
  1. Just for the people who don't know how to do this and looked at this thread to find a way. here you go!

    Pretty much if you have a root server running ubuntu 16.04. Open the screen/kvm and do the following :
    • sudo apt-get update
    • sudo apt-get install mysql-server
    • sudo mysql_secure_installation
    • systemctl status mysql.service (this is too test if you have installed it properly)
    This should be the output of systemctl status mysql.service!

    mysql.service - MySQL Community Server
    Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
    Active: active (running) since Wed 2016-11-23 21:21:25 UTC; 30min ago
    Main PID: 3754 (mysqld)
    Tasks: 28
    Memory: 142.3M
    CPU: 1.994s
    CGroup: /system.slice/mysql.service
    └─3754 /usr/sbin/mysqld

    if it does not show this do this then do the test command once again after this!
    • sudo systemctl mysql start
    And there you have installed it but im guessing you want to know the rest on how to get it setup with ranks and pex and stuff!

    • mysql -u root -p (after this you should be prompt with a password the password is the one you did on step 3)
    • CREATE DATABASE database_name; (Change datdabase_name to something else what ever you want but make sure you remember it!)

    • grant all privileges on database_name.* to 'username_you_want'@'localhost' identified by "choose_your_password_here";
    • flush privileges;
    • exit
    And there you now have your database!!!

    Now to add it to pex so all your servers can use it!

    • Make a copy of your permissions in your pex config first
    • Then delete your pex folder AFTER YOU MADE YOUR BACKUP!
    • Then reload all of your servers
    • In your game after that do /pex backend sql dont worry if you get a error thats good!
    • then there should be another pex folder there click on it and press on config
    • Then when your in the config change the "backend: file" to "backend: sql"
    • Below that there should be a backend called sql.
    • Now change that to what you put in the database in my case it will look like this!

    use-netevents: true
    debug: false
    allowOps: false
    user-add-groups-last: false
    log-players: false
    createUserRecords: false
    backend: sql
    changes: false
    basedir: plugins/PermissionsEx
    type: file
    file: permissions.yml
    type: sql
    uri: mysql://localhost/database_name
    user: username_you_want
    password: choose_your_password_here
    updater: true
    alwaysUpdate: false

    • Do that for each config for your servers and then reload each server and there you go!
    You now have a sql and pex connected to it! Now all you need to do is add the ranks and perms and stuff!

    If you want to protect your mySQL against attackers or hackers here are the commands to only allow your ip or to block other peoples ip from your server!

    On Ubuntu, the easiest way to save iptables rules, so they will survive a reboot, is to use the iptables-persistent package. Install it with apt-get like this:

    • sudo apt-get install iptables-persistent
    During the installation, you will asked if you want to save your current firewall rules.

    If you update your firewall rules and want to save the changes, run this command:

    • sudo invoke-rc.d iptables-persistent save
    The loopback interface, also referred to as lo, is what a computer uses to for network connections to itself. For example, if you run ping localhost or ping, your server will ping itself using the loopback.

    To accept all traffic on your loopback interface, run these commands:

    • sudo iptables -A INPUT -i lo -j ACCEPT
    • sudo iptables -A OUTPUT -o lo -j ACCEPT

    As network traffic generally needs to be two-way—incoming and outgoing—to work properly, it is typical to create a firewall rule that allows established and related incoming traffic, so that the server will allow return traffic to outgoing connections initiated by the server itself. This command will allow that:

    • sudo iptables -A INPUT -m conntrack --ctstate ESTABLISHED,RELATED -j ACCEPT
    You may want to allow outgoing traffic of all established connections, which are typically the response to legitimate incoming connections. This command will allow that:

    • sudo iptables -A OUTPUT -m conntrack --ctstate ESTABLISHED -j ACCEPT
    Assuming eth0 is your external network, and eth1 is your internal network, this will allow your internal to access the external:

    • sudo iptables -A FORWARD -i eth1 -o eth0 -j ACCEPT
    Some network traffic packets get marked as invalid. Sometimes it can be useful to log this type of packet but often it is fine to drop them. Do so with this command:

    • sudo iptables -A INPUT -m conntrack --ctstate INVALID -j DROP
    To block network connections that originate from a specific IP address, for example, run this command:

    • sudo iptables -A INPUT -s -j DROP
    In this example, -s specifies a source IP address of "". The source IP address can be specified in any firewall rule, including an allow rule.

    If you want to reject the connection instead, which will respond to the connection request with a "connection refused" error, replace "DROP" with "REJECT" like this:

    • sudo iptables -A INPUT -s -j REJECT
    To block connections from a specific IP address, e.g., to a specific network interface, e.g. eth0, use this command:

    • iptables -A INPUT -i eth0 -s -j DROP

    I do not give credit to myself for making pex or anything in this post. I give full credit to the makers of the plugin and software!
    #1 MGPro, May 21, 2017
    Last edited: May 22, 2017
    • Funny Funny x 1
  2. Good Tut but i recommend having UBUNTU in the TITLE so people get to this thread quicker
  3. To be honest if you're not showing how to setup mysql in Java this probably goes in server administration. Good tutorial though!
  4. Docker, Arch, Fedora, anything else?

    This isn't plugin development and then wrong section, not to mention there's a much better alternative for MySQL called MariaDB, and you should be able to patch security flaws on the server when you do this.
    E.g. my VPS runs docker with MariaDB, where all connections outside localhost and my servers are refused access. This is basic things you should mention when making such a tutorial.

    For readers: I'd throw this a die of 3, possibly a 4.
    Pretty sure that says "Spigot Plugin Development" I thought he may have posted in the wrong section accidentally.

    While I agree with mariaDB being a better implementation of's not going to patch any security flaws. The only known CVEs in MySQL are related to sub-components which are not installed by default (PluggableAuth being the big one), and would never be installed for our usage.

    MariaDB in no way shape or form affects these subcomponents.

    @OP some things I'd recommend adding:

    How to set up IPTables so that only IPs may access port 3306
    When to bind to (almost never), and it's implications
    Optimization of mysql via the /etc/mysqld configuration
    Basic operation of the mysql-client package for database management (ie view databases, tables, and table structure)
    How to clear tables with truncate
    How to delete IDs that match a value on a specific column
    How to update values that match a value on a specific column
  6. Sorry i posted it in the wong section and ill happly do one for MariaDB and if refusing access to locahost is basic why cant the people search it? and i will add it anyway ok?
    And this is for ubuntu Im ganna add it to the title rn as well ok?
  7. Yep sorry accidently put it in the wong section btw this is my first tutorial on spigot so im just guessing use to it all at this point!
    Anyway thanks!
  8. That person doesn't seem to know what he/she is talking about. There are 2 major ways to do this:
    1) Change the bind address in the /etc/mysql config from to 192.168.x.x or or localhost etc
    2) Set up IPTable rules

    Most people aren't going to be on a vlan or anything, so option 2 is going to be the only one. That's why I recommended you mention that.
    Hope that helps! PM me if you have any questions.
  9. I will add this right now thanks!
  10. That's pretty much the same, though. You should always refuse access from unknown IP addressed to 3306, no matter what, as long as some SQL is there
  11. As a bit of extra help and resources for Ubuntu 14/16/17 users, Digital Ocean has some great tutorials that apply to loads of linux hosting solutions. Very handy for those who want to setup LAMP on a barebone VPS;
  12. Ok, dont use ubuntu 16.04 LTS but 17.04 :D