Setting up a MySQL database while installing WordPress on their host has came a long ways compared to even 5 years ago. Most people experience an easy way to use cPanel, Fantastico, or Simple Scripts which provide a push button approach of installing and doing the heavy lifting for you such as your Wordpress files on your domain and creating the database. Ideally this post is for someone that is fairly new but has some experience with setting up WordPress.
Recently I came across the need to do it manually. Now, most hosting companies allow you to create an individual database manually and off to the races you are. Typically that entails a pretty control panel to make your life easier. On this current project, they were using what I refer to as a raw server. There is no easy control panel to quickly eliminate several steps to create a database for your new WordPress website. It was a Dedicated Windows Server.
This is where MySQL Workbench comes into play.
The goal I was accomplishing was to set up a WordPress blog within an existing site. That site was built on Bootstrap and was NOT currently using WordPress as it's “engine”. Essentially, a static website.
How to Get Started with MySQL Workbench For WordPress Database
If your dedicated or virtual private server does not already have MySQL Workbench installed, head on over to get it downloaded: Go HERE. This assumes it isn't already installed. I won't be going over on how to install it but the prompts are fairly straight forward to get things set up. Your default username will always be “root”. I recommend choosing a complex and strong password if you choose to leave your username the same.
Step 1: Assuming you have Workbench ready to roll…. open the program. You will come to the “home” screen where you should see something similar to this. Now, my screenshot here is showing you a couple “tiles' but that is only because I've created a few.
Step 2: You will want to select the “+” sign next to “MySQL Connections” at the top left hand side (main screen). Just give that ol' plus sign a click and up pops a window over your screen as shown below.
- Name: Enter a “Connection Name”. It can be whatever your heart desires.
- Connection Method: It defaults to “Standard (TCP/IP). I would just leave it alone. You have the option to change that though. It depends on a few other variables.
- Hostname: Will be auto populated by default (write that down!!!!)
- Port: Auto populated as well. (As Van Wilder said, “Write that down.”)
- Username: If you have chosen it to be “root”… it will be sitting there for you.
- Password: I would go ahead for good measure and click on “Store in Vault”. A new box will pop up. Enter your password and hit save. If all is well… you'll be aware.
- Default Schema: I would say just leave blank for now because we will cover that shortly below.
Test Connection button: Click this and it will tell you if you are good. Generally you will be fine but worth doing.
Hit “OK” button to finish.
Overall, you don't really have to touch much here to move on in the process. I am sure you are relieved at this point.
Step 3: You are back on the “Home” screen. Next you will select your Connection “Insert what you called it here” box. In my example, I called it “test”.
Step 4: A new type of window has now popped up for you to look at. This area is where you could write a Query by selecting multiple databases or “Schemas” to pull data up to export, view, or correct. No worries, you won't have to mess with that during this process. 🙂
- Lower left hand side, you will see a box menu called “SCHEMAS”.
- You will see some existing ones that Workbench includes to give you some ideas.
- Right below it, right click your mouse and a box will pop up.
- Select “New Schema”.
Step 5: Give your new Schema a name and hit “Apply” to move on. Make sure you pay close attention on the instructions on the right on the nomenclature. They want alpha-numeric. Spaces should be avoided and use the “_” (underscore) to separate. I would avoid calling it “wordpress” or “wp”. You could go with something like “mycoolblog_2014111”. Just mix it up some.
Remember what Van said? Yes, right this name down!
Step 6: After you hit “Apply” from the last step, you will receive another pop up box. Nothing has to be down here except hit “Apply” button again. This is creating the basic framework for the database set up. It is creating this by a “Script”.
Step 7: You will see another screen after the above action and simply just hit “Finish”. The pop up will close and you are a step closer to greatness.
Step 8: Looking back at the “SCHEMA” section on the lower left… I called mine “new_schema” but don't be so cool like me and use a better name like something in Step 5.
Completing Manual WordPress Installation
This part is assuming you have unzipped the zip file from WordPress.com and have moved the unzipped folders and files to your folder where you want them. You have a few fields here to fill out.
- Database Name: This was Step 5 above. Enter your name exactly.
- Username: If you have left name as “root”, then this is what you use.
- Password: Input your crafty mile long password you created once you installed MySQL Workbench in the beginning if you had to. Otherwise, you will know it. If not… you will have to go back and create a new user assigned to this database (SCHEMA). Creating Username & Privileges
- Database Host: For our example here… you will need to remember what you wrote down from Step 2. WordPress will default it to “localhost” but that usually is left like that if you are on a managed server. It is an IP address along with the port. You will conjoin the two by a semicolon. It probably will look like this: 220.127.116.11:3309
- Table Prefix: I would just leave as “wp_”.
Hit Submit button. Carry on completing the next steps for your WordPress websites. When you move on after entering your database information…. WordPress will do the heavy lifting in creating the “tables” and such needed inside of the database. That is a question a lot of people don't understand or realize. ******You may be prompted to create a “wp-config.php” file after inputting all your DB info. WordPress comes with a “sample config file” (found in root folder of installation) that you can open up and input these above credentials in if need be. After you do that, just simply rename that file to “wp-config.php” and move forward.
Final Thoughts on Your WordPress Database in MySQL Workbench
I sincerely hope this has helped you in some way. The above steps helped accomplish my task. If you were like me, you probably searched through a lot of pages to find something close to accomplish this. You should experience a working WordPress blog with minimal issues. Of course, a lot of outside variables come into play but this should get you on the right track.