Web Served, Part 4: Get your database on
- Thread starter JournalBot
- Start date
finding these articles really useful. I mostly do design and front-end development on shared and/or dedicated hosts, but the cheap VMS offers on lowendbox.com and vpslist.net have got me interested in kicking around a lean build.
Upvote
5
(5
/
0)
As an item of clarification: If the TCP connections to the DB is shut off, I can no longer use the MySQL Workbench tools from my local development machine yes?
Any kind of tricky things to suggest so that I could still use that and still be safe?
Any kind of tricky things to suggest so that I could still use that and still be safe?
Upvote
0
(0
/
0)
Just a hope for future editions, but it would be nice to perhaps link to a few guides on alternative methods. These guides are a godsend because most of the time I run out of time to finish making a webserver before needing to host content and I'm planning this as a weekend project, but don't want to fall into the same traps again if I deviate from the guide with other implementations (such as javascript).
Upvote
0
(0
/
0)
This has been a terrific series. I love your choice of nginx and maria. It's too easy to get mired in the apache/mysql routine to the point that one forgets that there are really good alternatives out there. Keep up the good work!
Upvote
7
(7
/
0)
Part 4 already? How did I miss part 3, now I'm a week behind! Is there a way to "subscribe" to a series or maybe filter articles by author?
Also, put me down as interested in reading a future article on NoSQL databases.
Also, put me down as interested in reading a future article on NoSQL databases.
Upvote
0
(0
/
0)
How can MariaDB work exactly like MySQL while also containing fewer bugs? That's contradictory, no? <3
Upvote
4
(5
/
-1)
A lot of people have a single login to access the db that has full access to the db in question even for just displaying content on a page. I like to have different levels of access setup so most page operations only have access to what they need to - this helps mitigate any injection issues that you may have because of poor data validation techniques. For instance 99% of the site is read only, and only to certain portions of the db. (IE only login portions of the site can actually be compromised to gain access to user tables, etc..).
I have seen so many CMS sites where a simple injection attack from a user upload section can pull user login tables, etc...
Just remember the most secure server is one that is not online
I have seen so many CMS sites where a simple injection attack from a user upload section can pull user login tables, etc...
Just remember the most secure server is one that is not online
Upvote
4
(4
/
0)
I'll be talking through using a single DB user for each web app (like, one DB user for the forum software, one DB user for wordpress, and so on), but not any more granular than that. All of the web apps I'm planning on discussing don't appear to offer a way to use more than a single account, so I'm not sure if it's possible for mainstream stuff to get more granular, though I'd love to hear more detail about how and what you're doing.danstl":31iz3pne said:A lot of people have a single login to access the db that has full access to the db in question even for just displaying content on a page. I like to have different levels of access setup so most page operations only have access to what they need to - this helps mitigate any injection issues that you may have because of poor data validation techniques. For instance 99% of the site is read only, and only to certain portions of the db. (IE only login portions of the site can actually be compromised to gain access to user tables, etc..).
I have seen so many CMS sites where a simple injection attack from a user upload section can pull user login tables, etc...
Just remember the most secure server is one that is not online
Upvote
2
(2
/
0)
I'm really enjoying this series. It has a great balance of readability, ease of use, and describing why things are done.
Really looking forward to the next installment.
Really looking forward to the next installment.
Upvote
4
(4
/
0)
This series has been awesome - probably my favorite series ever from ars and that's saying a lot (been reading ars for years now).
Any chance the series is going to get into mail servers?
Thanks again for a fantastic series.
Any chance the series is going to get into mail servers?
Thanks again for a fantastic series.
Upvote
7
(7
/
0)
I have really enjoyed the series. I would like to see more web dev stuff! +1 for going open source.
Upvote
1
(1
/
0)
If it's on a remote machine, you could use an SSH tunnel to access it.johneee":24fjxb3i said:
Securing the connection between MySQL and MySQL Administrator using an SSH tunnel
Upvote
1
(1
/
0)
I love this series. I tried making a web server on and old G4 Mac Mini but the incompatibilities with a lot of the "recommended" Linux web-hosting tools and a PPC slowed me down to where I couldn't this summer. I will definitely be looking to try to implement this series using a more recent computer build. I even have a spare mini-ITX motherboard available.
Upvote
0
(0
/
0)
johneee":16r02a75 said:
You could use SSH and setup a local port forward from your local machine to the remote machine's MySQL port.
On Windows PuTTY makes this pretty easy, and for Linux and OS X it's a one liner. You can find plenty of examples and documentation with a quick Google search.
Upvote
0
(0
/
0)
A couple of quick MySQL performance tuning notes (that don't require monitoring traffic):
1) Use InnoDB
2) Set your buffer pool size to be as large as possible (80% of your free memory is a decent rule of thumb)
3) Use a SSD
In your /etc/my.cnf:
This will make your database hardened against crashes and perform better in most cases, without getting pedantic about tuning.
Non-performance related - SET UP A BACKUP! MySQL comes with a decent tool for doing logical database backups - mysqldump. It will work fine until your database is in the multi-gigabyte size, at which point you're beyond this guide anyways.
1) Use InnoDB
2) Set your buffer pool size to be as large as possible (80% of your free memory is a decent rule of thumb)
3) Use a SSD
In your /etc/my.cnf:
Code:
default_storage_engine=InnoDB
innodb_buffer_pool_size = 3GThis will make your database hardened against crashes and perform better in most cases, without getting pedantic about tuning.
Non-performance related - SET UP A BACKUP! MySQL comes with a decent tool for doing logical database backups - mysqldump. It will work fine until your database is in the multi-gigabyte size, at which point you're beyond this guide anyways.
Code:
mysqldump --single-transaction <database_name> | gzip -c > /tmp/$(date '+%Y-%m-%d')_backup.dump.gz
Upvote
9
(9
/
0)
Another great installment to this series! Thank-you Lee. What I really enjoy about your articles is that they're littered with great technical tidbits and advice (Console.app, DokuWiki).
My wife has been asking for a home WordPress server for some time, and you're going to make me look very good for Christmas. (-:
One very minor issue: in the article, the command "rename user root@localhost to bob@localhost" is missing its semi-colon. Someone following along with less experience may not know what to do at the -> prompt. Here's what I got:
MariaDB [(none)]> rename user root@localhost to squareroot@localhost
-> ;
Query OK, 0 rows affected (0.00 sec)
Looking forward to part five!
My wife has been asking for a home WordPress server for some time, and you're going to make me look very good for Christmas. (-:
One very minor issue: in the article, the command "rename user root@localhost to bob@localhost" is missing its semi-colon. Someone following along with less experience may not know what to do at the -> prompt. Here's what I got:
MariaDB [(none)]> rename user root@localhost to squareroot@localhost
-> ;
Query OK, 0 rows affected (0.00 sec)
Looking forward to part five!
Upvote
0
(0
/
0)
MariaDB is a very interesting project, but I think it's a bit flippant to recommend such a small project without weighing the community base. Especially when you've just dismissed the entire world of NoSQL for being too fringe. Yes it's compatible, and seems well maintained, but it's still an extremely small project. The performance gains a small website is going to see from using MariaDB are pretty minimal when compared to the difference they'll find in community support.
According to Monty himself, MariaDB has ~100k users. MySQL has 10-50 million. It's certainly not the only factor, but you're taking a real risk here.
Monty's user base comment:
http://stackoverflow.com/questions/4069 ... d-projects
According to Monty himself, MariaDB has ~100k users. MySQL has 10-50 million. It's certainly not the only factor, but you're taking a real risk here.
Monty's user base comment:
http://stackoverflow.com/questions/4069 ... d-projects
Upvote
1
(2
/
-1)
DougHW":1dwil655 said:
MariaDB is essentially the core mysql with patches on top of it. Not to mention that the folks Monty hired are the ones who worked on MySQL originally (and are arguably the only ones who are intimately familiar with the optimizer).
The patches add freedom, stability, features and performance enhancements with very little risk (particularly for a personal web server). Given that they are binary compatible, why wouldn't you recommend them?
Upvote
3
(3
/
0)
I've found offloading compression and writing to another host super helpful if your server is seeing lots of use during the backup. innobackupex is also a great solution for larger databases, and you can similarly offload compression/writing to another host with it.falcolas":3ccu0f2n said:
Code:
mysqldump ... | ssh user@host 'tar -cvzf backup.tar.gz -'
Upvote
2
(2
/
0)
Krutawn":1921dwq3 said:
Well, you could start by running Ubuntu Server on that thing. There's a community-supported port.
One major caveat is that no third-party software is compiled for PowerPC. So, when guides tell you to add third-party repositories and download programs from them, you have to add the source repositories and run apt-build on them. It's slower, it works only with programs that include source, and the programs have to be well-written to be portable across processor architectures.
Upvote
1
(1
/
0)
Oh, Lee. Obscurity is the worst reason to choose PostgreSQL. It's open-source with a BSD-style license, and it's used by a lot of major companies. More web sites use MySQL, but PostgreSQL is not exactly obscure.
Probably the best thing about PostgreSQL is its excellent documentation. PostgreSQL has always been concerned about correctness, and part of that is having comprehensive and accurate documentation. MySQL is available under GPL, so MariaDB is GPL, but the documents were proprietary to MySQL. Monty had to write or acquire all new documents that aren't as comprehensive.
Probably the best thing about PostgreSQL is its excellent documentation. PostgreSQL has always been concerned about correctness, and part of that is having comprehensive and accurate documentation. MySQL is available under GPL, so MariaDB is GPL, but the documents were proprietary to MySQL. Monty had to write or acquire all new documents that aren't as comprehensive.
Upvote
3
(5
/
-2)
falcolas":dhcrc6bl said:
I wouldn't back up to /tmp. Most recent distros mount /tmp in RAM, and I'm pretty sure Umbongo does it as well.
Upvote
0
(0
/
0)
Great series. However, as a Linux newbie, I've found your choices for software a bit difficult to find support for. For instance, I know it may not be cool anymore to run LAMP, but setting up an Ubuntu server as a LAMP server, then installing WordPress on top of that was super easy. In fact, I think it's easy enough to say you probably couldn't write more than one article about it. A few Google searches is all it took to get me up and running on LAMP. Finding what I've needed with your suggested build was difficult as most of what I was looking to do was not fully supported in nginx.
As I said, though, this is a great series and I have learned a lot from it. Thanks again!
As I said, though, this is a great series and I have learned a lot from it. Thanks again!
Upvote
-1
(0
/
-1)
It all depends on what you want to do and how you want to do it. Wordpress is fully supported on Nginx and it works perfectly (which we'll get to soon). It isn't as easy to set up on your own as it is going with a prebuilt LAMP stack, but there are varying degrees of "easy", too—the easiest would be to simply use hosted WordPress. Then you're off the ground even faster than setting things up under LAMP.
The goal here is to take you through setting up a personal web server from back to front, with eye towards security and performance but without going crazy. Ubuntu with the LAMP role installed uses Apache prefork; for a personal site, no big deal, but making it performant enough to hold up under a slashdotting (or an Arsdotting or a fireballing or a redditing or a farking) takes a considerable amount of configuration. We're catching a lot of that as we go along. Not that everyone's personal site must be prepared for hordes of redditors kicking down your door, but it doesn't hurt to have a good foundation already in place.
The goal here is to take you through setting up a personal web server from back to front, with eye towards security and performance but without going crazy. Ubuntu with the LAMP role installed uses Apache prefork; for a personal site, no big deal, but making it performant enough to hold up under a slashdotting (or an Arsdotting or a fireballing or a redditing or a farking) takes a considerable amount of configuration. We're catching a lot of that as we go along. Not that everyone's personal site must be prepared for hordes of redditors kicking down your door, but it doesn't hurt to have a good foundation already in place.
Upvote
3
(3
/
0)
I'm having trouble with SQL Buddy. Getting an error when trying to log in: "There was a problem logging you in." Don't get any errors in nginx's error.log, nor anywhere else I know to check. Using the correct username and password, as check by running mysql -u username -p. Any ideas what I'm doing wrong?
Upvote
1
(1
/
0)
romanlevin":kaqcn0aq said:
I had the exact same issue, my problem was failing to properly tell PHP to use MariaDB's Unix socket (i.e. the change "mysql.default_socket = /var/run/mysqld/mysqld.sock"). Not sure if it's the same problem but probably worth checking.
Upvote
2
(2
/
0)
Porkface: Looked there already. Nothing out of the ordinary.
Shaitan: I actually did mess up my socket configuration, but it showed up in nginx's log and I fixed it.
Shaitan: I actually did mess up my socket configuration, but it showed up in nginx's log and I fixed it.
Upvote
0
(0
/
0)
Decade":1w6vnjzs said:
I did get Ubuntu Server running, and most of a LAMP-server working, but I was unable to find a version of PHP that was compatible with both a PPC system and the other their-party tools I had installed.
Oh well, I got the thing for $35 and currently have it set up as a NAS for my apartment.
Upvote
0
(0
/
0)
Hi new MariaDB users (and happy MariaDB users too).
I want to confirm the good results obtained with MariaDB vs MySQL. I'm a developer that I inverted a lot of time optimizing queries for MySQL without good results until I discovered MariaDB. You can read my related post in http://www.saltos.net/portal/en/detail/ ... -mysql.htm.
Josep.
I want to confirm the good results obtained with MariaDB vs MySQL. I'm a developer that I inverted a lot of time optimizing queries for MySQL without good results until I discovered MariaDB. You can read my related post in http://www.saltos.net/portal/en/detail/ ... -mysql.htm.
Josep.
Upvote
0
(0
/
0)
You can also place customizations to your maria/mysql db config in /etc/mysql/conf.d/*.cnf instead of modifying my.cnf directly.
That way, you can place all your customizations in that directory, while leaving the default configuration file intact, which has benefits when upgrading maria/mysql.
In order to support international users as much as possible I've changed the default character set to utf8 (from latin1) and collation settings to utf8_unicode_ci (from latin1_swedish_ci) by creating /etc/mysql/conf.d/globalization.cnf with the following contents:
[mysqld]
character_set_server=utf8
collation_server=utf8_unicode_ci
skip-character-set-client-handshake
That way, you can place all your customizations in that directory, while leaving the default configuration file intact, which has benefits when upgrading maria/mysql.
In order to support international users as much as possible I've changed the default character set to utf8 (from latin1) and collation settings to utf8_unicode_ci (from latin1_swedish_ci) by creating /etc/mysql/conf.d/globalization.cnf with the following contents:
[mysqld]
character_set_server=utf8
collation_server=utf8_unicode_ci
skip-character-set-client-handshake
Upvote
0
(0
/
0)
Thanks for the info!leedo":3f850kd1 said:
Does replacing utf8 with utf8mb4 and utf8_unicode_ci with utf8mb4_unicode_ci do the trick?
Any downsides (apart from disk usage)?
Still, some unicode is better then none ;-)
Upvote
0
(0
/
0)
Honestly not entirely sure. I just learned this recently when our forums were unable to insert emoji. A little online reading indicates that utf8mb4 shrinks the max InnoDB index length by quite a bit, so there could definitely be some downsides.PietjePuk75":344tel5y said:
Upvote
1
(1
/
0)
- Status