[Bukkit guide] MySQL

Also, On ubuntu you can just use

Code:

sudo service mysql start

instead of

Code:

sudo /etc/init.d/mysql start

 

zipron could you add me on skype and walk me through setting up sql on my server. I am using Pex and have just created a hub server but my factions server is still not connected to bungee. Whenever I changed it to offline mode it made all of my players ranks and such change to where everyone was a new player. I obviously unhooked it from bungee and put it back to online mode and it went back to normal, everyone had their correct ranks and inventories... etc. I was hoping you could walk me through setting up the sql so I can transfer the ranks from the faction server over to the other servers and then somehow make it so the ranks stay when I add the server to the bungee network.

 

I have some more in-depth tutorials on my site related to this. They are step-by-step so they are quite lengthy as well as including information on how to automate/script normal activities such as backup/restore and other tid-bits of info an administrator needs.

Installing Ubuntu Server 14.04
Installing Ubuntu Server 12.04
Installing MySQL on Ubuntu Server 12.04

LHammonds

 

Ok, This helped me a lot, but for my setup, with MySQL running on the same computer as my server, I had to to some other stuff. Maybe stuff changed in a newer version of MySQL or whatnot.

First...debian 7 on openvz vps....cauldron(used to be called MCPC+) build 89...which is the final build for 1.6.4...

Two things I had to do to get it to work. Here is something to know, it helped me in this. A MySQL "username" is a name@ippaddress, so you are not "allowing" the account to come from an ip, you are making a NEW username. If you grant access to two ips and a localhost, you have three user names accountname@'localhost', accountname@'firstip' and accountname@'second ip'
When logged into MySQL as root

Before creating user, create the database for the user.

create database DBname;

DBname is a name of your choice, I use the name of the plugin its for, same as the username


When creating the user, create it with the right ip/local designation. If you create it without one, as suggested in the tut, it will create accountname@% , and I think that interfered with my logins.

instead of this
CREATE USER bukkit IDENTIFIED BY 'password1';

do this
CREATE USER bukkit@'localhost' IDENTIFIED BY 'password1';

or ip if your not local host, btw, I had luck with localhost and not with 127.0.0.1

then instead of this
GRANT ALL ON *.* TO bukkit@'127.0.0.1';

do this
GRANT ALL ON DBname.* TO bukkit@'localhost';

Like I said, localhost seemed to work best, in theory 127.0.0.1 is the same or better, but mine happened to work when I used localhost so I use it.

If you already messed it up, like I did list the users with
SELECT user,host FROM mysql.user;

see how the accounts and ip's work now?

leave all 4 root accounts (I didn't...woops, that sucked trust me) alone, but you can clear out all the extras you made with

drop user 'idiot'@'localhost';
or
drop user 'idiot'@'127.0.0.1';
or
drop user 'idiot'@'23.123.12.22';

whatever, you get it already.

Then test your MySQL database, try to login into the database with the user you set up.
If you cant connect, you got a problem, and it took you a split second to figure it out rather than waiting for the server to boot for ever cfg change to see if it finally works.
For example, for the user bukkit at your bash prompt, type

MySQL -u bukkit -p DBname

It will prompt for the password you for that user, then if your setting are good, you will get the MySQL prompt. now type exit to leave and setup you plugin!!

 

I explained to somebody a long time ago how to setup an existing MySQL database to let a plugin connect to it...I wonder where it is...

Ah, found it:

1. You must create a database.
2. You must create a login account.
3. You must grant permissions for that login account to that database.
4. You update your plugin configuration to specify the MySQL database server name/ip, the database name, user ID and user password.
5. Most plugins will create the tables and populate the data as long as you have done the prior steps.

How you do all of these depends on the tools you use. If you have access to the server command prompt where the database is installed, you can run the mysql command-line. If you access the database using PhpMyAdmin, then you can run queries inside that. If you are running MySQL on a Windows machine and you have WorkBench installed, you can use that utility. I'm sure there are several other ways as well and depending on how you connect will decide the exact steps you take.

Creating the database and user accounts are the super easy parts that are extremely easy to find via google.

The step to grant permission can require a bit more knowledge of how your server is setup and can determine if you pass or fail in your connection attempt. For example, if your MySQL database is run on the same server as your minecraft server, then you can grant access for the user ID to the database as "local only" access. If your database is on a different server, you can grant access for the user ID to that database for "that server name" only...meaning the user ID is only allowed to connect from your specified minecraft server machine/IP. Another option is to grant access for that user ID to the database as "global" access meaning that ID can connect to the database from anywhere in the world...which is typically not as secure but can be handy if your machine name changes or you have multiple servers that connect to the same database and you use the same ID.

Here are some examples of SQL syntax for setting up a database called "minecraft" with an ID of "minecraftuser" and password of "mypass123" but I STRONGLY suggest you never use this exact ID/pass combo now that the entire world knows about it.

Example #1 - Database and Minecraft on same machine

Code:

CREATE DATABASE minecraft;
CREATE USER 'minecraftuser'@'localhost' IDENTIFIED BY 'mypass123';
GRANT ALL PRIVILEGES ON minecraft TO 'minecraftuser'@'localhost';
FLUSH PRIVILEGES;

Example #2 - Minecraft on separate machine called srv-minecraft

Code:

CREATE DATABASE minecraft;
CREATE USER 'minecraftuser'@'srv-minecraft' IDENTIFIED BY 'mypass123';
GRANT ALL PRIVILEGES ON minecraft TO 'minecraftuser'@'srv-minecraft';
FLUSH PRIVILEGES;

Example #3 - User ID able to login to database from anywhere

Code:

CREATE DATABASE minecraft;
CREATE USER 'minecraftuser'@'%' IDENTIFIED BY 'mypass123';
GRANT ALL PRIVILEGES ON minecraft TO 'minecraftuser'@'%';
FLUSH PRIVILEGES;

I really should add this on my tutorial site somewhere.

LHammonds