[Bukkit guide] MySQL

Discussion in 'Bukkit Help' started by zipron, Dec 18, 2011.

  1. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    [Bukkit guide] MySQL

    So here we are again this time with a short guide about MySQL. Feedback and likes are always appreciated =)

    Table of contents
    - MySQL? WTF??
    - Setting up a server
    - Endings
    - Useful links


    MySQL? WTF??
    So there are some plugins wich need a database to work with. Some support flatfiles, some support other database types. If a plugin really needs a database, the chance it is a MySQL database is big. MySQL (SQL = structured query language) is a way of hosting databases. It is very powerfull and that is also the reason some plugins want to use it. In this guide I will only cover the installation of the server on linux and windows and I will show you how to config things right so your plugins can work with your MySQL server.

    Setting up a server
    So first thing you'll need to do is downloading the server software. For Linux (ubuntu), you can use this easy command:
    Code:
    sudo apt-get install mysql-server
    Follow the instructions given in the instalation. At a point, it will ask for a root password. This is an important password, so be sure you remember it!

    For Windows you'll have to download the MySQL server software from the MySQL site, you'll have to create an account on the site to download the software:
    http://www.mysql.com/downloads/mysql/
    Run through the installation wizard. Press next (and choose deailted configuration) untill it asks for security settings. Here you'll have to choose a root password, same as the linux users: don't forget this! When all is installed, your server is ready to use.

    So what we now have to do is allowing the connection between the bukkit and MySQL server and (if you want that) allowing remote access from other computers.

    In Linux you can start your MySQL server by using this command:
    Code:
    sudo /etc/init.d/mysql start
    
    If you have ubuntu server installed, MySQL will start up automatically so you won't have to start it. Use this command to start your commandline:
    Code:
    mysql -u root -p
    
    After this you'll have to give a password. This is the password you filled in in the installation file!

    Windows users will have to go to start > programs > MySQL > MySQL server > MySQL command line
    When your server is started, it will ask for a password. This is the password you filled in in the installation file!

    When this is done, you are logged in as the root user of your server. Now we have to make a user for bukkit and allow it to access the databases. Using following code will create a user with a password. Be sure to change "password1" into the password you want and you can choose another name if you want to.
    Code:
    CREATE USER bukkit IDENTIFIED BY 'password1';
     
    output:
    mysql> CREATE USER bukkit IDENTIFIED BY 'password1';
    Query OK, 0 rows affected (0.00 sec)
    
    Now we want this user 'bukkit' being able to access the database. We can bind the user to a certain IP adress. Normally, your MySQL server is on the same machine as your bukkit server wich means we can use the localhost adress: 127.0.0.1. If not, you can grant access from all IP's or grant access to a certain IP. This will mean your plugins can only enter the database if they have that IP adress.
    Localhost:
    Code:
    GRANT ALL ON *.* TO bukkit@'127.0.0.1';
     
    output:
    mysql> GRANT ALL ON *.* TO bukkit@'127.0.0.1';
    Query OK, 0 rows affected (0.00 sec)
    
    All IP's:
    Code:
    GRANT ALL ON *.* TO bukkit@'%';
     
    output:
    mysql> GRANT ALL ON *.* TO bukkit@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    Binding an IP adress:
    Code:
    GRANT ALL ON *.* TO bukkit@'<ipadress>';
     
    output:
    mysql> GRANT ALL ON *.* TO bukkit@'<ipadress>';
    Query OK, 0 rows affected (0.00 sec)
    
    Now restart MySQL to make this changes work: on Windows just restart. On Linux enter following command:
    Code:
    sudo /etc/init.d/mysql restart
    
    Now the user 'bukkit' can access all databases. If you're on Linux, we have to do one more thing: we have to go to the MySQL config file and change the "bind-adress" property if you want to be able to access the MySQL server from another computer. This is only needed if your MySQL server is on another server than the bukkit one is. So if that is goto
    Code:
    zipron@zipronServer:~$ cd /etc/mysql/
    zipron@zipronServer:/etc/mysql$ nano my.cnf
    Now search for:
    Code:
    bind-address          = <ipadress>
    
    and change it to:
    Code:
    # bind-address          = <ipadress>
    
    Again, you only need to do this if your bukkit server is on another host then you are, or if you want remote access from another computer in your database.

    Now a few more things:
    - if you want remote access, you'll need to port-forward the MySQL port. (3306 by default)
    - The plugins using a database will ask for a host, a user and a password. We create a user and a password in the section above. The host is the IP adress the MySQL server is on. If it's on the same server, you can use the localhost adress: 127.0.0.1. Otherwise you'll need the IP of the MySQL server.

    Endings
    I hope this helped some people out. I made this guide because I saw someone having issues with MySQL and maybe some others can use it too. Now before ending I want to mention that no guide is 100% reliable and correct. So if you see any mistakes, please point them out, me and everyone who uses this guide will be thankfull =)

    Useful links:
    MySQL download page: http://www.mysql.com/downloads/
    MySQL manuel: http://dev.mysql.com/doc/refman/5.1/en/index.html

    that's it, have fun =)
    zip

    This post has been edited 4 times. It was last edited by zipron Sep 16, 2012.
  2. Offline

    Karlkorv

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    No Mac? Aww...
  3. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    No I'm anti-mac, sorry =)
    zip
  4. Offline

    akaBruce

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Thanks zipron! One small question...Do I portforward both TCP and UDP of the port 3306? (Which I have already done) Also, do I create a database by typing CREATE DATABASE minecraft;?

    This post has been edited 2 times. It was last edited by akaBruce Dec 18, 2011.
  5. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Yes on both questions :)
    zip
  6. Offline

    essjaysammy

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Gah I'm an idiot because i can't work this out..
    Can i 'please' ask you for help.. :)
    I want to setup OwnBlocksX plugin which requires MySQL.
    Maybe I'm entering it in wrong, idk.
    I've downloaded MySQL v5.5 and installed it per your instructions.
    I'm entering the right thing I'm assuming, but i get back errors.
    I'm sure it's me.. lol

    I'm running Windows7 64bit and the server is run from my pc.
    Thank's in advance for any help you might be able to give me.
  7. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Can you copy-paste the errors in here? =)
  8. Offline

    chilldy

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Thanks for this. Bookmarked as people ask for this all the time!
  9. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    =) question: what does bookmarked means? xD
  10. Offline

    essjaysammy

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Thank's zipron, ill get those errors to you shortly.. Not at home.
  11. Offline

    chilldy

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    :confused: It means that I saved this page under shortcuts so I can use it as a reference in the future.
  12. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    I see, ok =)
  13. Offline

    mmorton89

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    quick question does it matter if the SQL database is on the same machine as the server or should I put it on my home machine? my server is on a dedicated service. Would it be better to use my home computer or the dedicated server?
  14. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    consider this:
    If you want it to be on your home computer, you can. Just use the correct IP, username and password, grant privileges on all IPs, or your server IP, open the correct port and it'll work. Now the problem is that your pc has to be online always, witht he MySQL server running. Yes it is possible, and it isn't very good.

    What you can do (if your MC dedicated server doesn't support MySQL): search a webhost wich allows MySQL access (there are free services out there, I think of 000webs as example now). Configure it correctly and it'll work. Now also know that hosting your database in an extern place, isn't the best way of doing things, because it will ask more bandwith usage and it will slow the communication between the server and the database. Now this is ofcourse not the biggest issue, but in my opinion, best thing you can do is hosting it on the same machine as your dedicated server (ask your host about the possibilities)

    zip

    This post has been edited 1 time. It was last edited by zipron Feb 10, 2012.
  15. Offline

    orangemars2000

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Help!
    Firstly, when i downloaded, it didn't ask for root password.
    Then when I tried to open it, it closed itself, kinda like java.
    Help me! what am i doing wrong?!
  16. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    If you installed it, it asked for a root password, if it closes, it means that you fill in the wrong password. So if you don't remember, you can search for the config file (program files > MySQL > somewhere here) and change your password there, or reinstall MySQL

    zip =)
  17. Offline

    orangemars2000

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Ah, i found the problem!
    Tell Windows users to donload the FTP one, not HTTP
  18. Offline

    orangemars2000

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Now it's saying I have an error in my SQL syntax, when i try to put in the first code?
  19. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    make sure alwyas to use ; after a statement =)
  20. Offline

    orangemars2000

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Ok, I have that fixed, but do i always use 127.0.0.1.?
    Or do I use the IP of my server (that other people use)?
    Or my IPv4?
    Probably stupid question though.

    This post has been edited 1 time. It was last edited by orangemars2000 Mar 25, 2012.
  21. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    you have several options, but if your MySQL server is hosted on the same machine as your bukkit server, you use 127.0.0.1 =) if hosted on another place (not reccomanded) you'll have to use the IP of that host

    zip
  22. Offline

    Wendo

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    One warning, port forwarding to MySQL is a risk. Don't do it unless you absolutely can't live without remote access to this database. You're better off connecting to the server via SSH or some other remote access than trying to remote access directly into MySQL.
  23. Offline

    Lolmewn BukkitDev Staff

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Why is this? I thought MySQL is quite secure.
  24. Offline

    Wendo

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    It is, that doesn't mean that most people allow root to log in from any ip and use the password password.

    In 99% of occasions, there is no reason to have a database exposed to the internet since all the servers that actually use it are on the same local network.

    Not to mention, most people that follow this, won't know to keep up to date with MySQL releases and security updates
  25. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    As I mentioned in my tutorial, in Linux you'll have to make another account, by default (the linux version of MySQL) doesn't allow root access from remote connections, so that isn't a problem. I don't know how it is in windows though

    zip =)
  26. Offline

    orangemars2000

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    I'm back with moar errors D:
    when i type in:
    GRANT ALL ON *.* TO bukkit@'127.0.0.1';
    I get:
    Can't find any matching row in the user table
  27. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    That means you haven't create the user bukkit, or something went wrong when you did. When you're in your MySQL console try this:
    Code:
    SELECT user FROM mysql.user;
    
    which should give you a list of all users, check if bukkit is there =)
    zip
  28. Offline

    mdo13

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    You know, there is a mac version too. Same link for windows.
  29. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    I don't have any expierience with mac, so I don't put it in this guide
    zip
  30. Offline

    zipron

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    it should ask a root password, otherwise it means that you've installed it incorrectly, are you sure you hadn't an earlier version installed?

Share This Page