editing mysql database file

nano /path/to/file should do, it's a basic command line text editing program.

Helveticus,

If the file size is an issue can't you zip of the file, download it, edit it, zip it up and then unzip it?

BTW, what changes do you need to make to the MySQL file that you can't make in phpmyadmin?

I don't know phpmyadmin. How do I use it?

Or else I can connect graphically to the linode, i.e. via en GUI. How can I do this?

> If the file size is an issue can't you zip of the file, download it, edit it, zip it up and then unzip it?

The zip is still 20MB. ;)

@Helveticus:

Of course, I can edit the database file on my local host
So how are you editing the MySQL DB on your local computer?

More importantly, why can't you do the same exact thing on your VPS system?

Why do you wish to edit it as a file? There's much greater potential for error than editing directly through MySQL

@Helveticus:

I don't know phpmyadmin. How do I use it?

You download it from the phpMyAdmin website, unpack it on your Linode, then setup a virtual host for it in your web server. If the MySQL database is on the same machine as phpMyAdmin, further configuration is usually not needed, but still recommended.

It's a far better idea to install phpmyadmin from the distribution's packages than from the phpmyadmin website… At least then there's a better chance it won't get exploited for being out of date.

@Guspaz:

It's a far better idea to install phpmyadmin from the distribution's packages than from the phpmyadmin website… At least then there's a better chance it won't get exploited for being out of date.

Depends on whether or not the distro maintainers do a good job at keeping the package up-to-date. Not all distros will have recent versions.

> It's a far better idea to install phpmyadmin from the distribution's packages than from the phpmyadmin website

How can I do this?

And then, how can I use phpmyadmin with my database file when it is installed?

Phpmyadmin has had a dubious security track record, and personally, I don't see the need for a FisherPrice-esque web interface just to manage your databases.

Either learn MySQL command line (it isn't rocket science), or use a client based GUI like HeidiSQL or SQLyog (both are WAAAAAAY more secure then a web based db interface).

@Helveticus:

> It's a far better idea to install phpmyadmin from the distribution's packages than from the phpmyadmin website

How can I do this?

And then, how can I use phpmyadmin with my database file when it is installed?

Use your distro's package manager to install it that same way you'd install anything else (apt-get install, yum install, pacman -Syu, …). As for how to use it, you'd need to locate where your package manager put it, set up a subdomain/vhost in your web server to point to it, then visit your subdomain. It should be fairly straightforward from there.

I agree with vonskippy that it's better to learn how to use MySQL from the command line. Something like phpMyAdmin should, at best, be a temporary solution to help you visualize what you're doing until you can memorize the commands.

You can also try MySQL Workbench which is cross platform, free and developed by the MySQL team.

> Either learn MySQL command line (it isn't rocket science), or use a client based GUI like HeidiSQL or SQLyog (both are WAAAAAAY more secure then a web based db interface).

I know SQL, but I know only how to query the database etc., i.e. select statements and so on. But I don't know how to use it on linode, i.e. how to connect and log in into the database and so on.

How do I use MySQL Workbench, HeidiSQL or SQLyog with the database as a GUI? I'm only connected via command line with the linode. Did you mean that I use MySQL Workbench on my pc and connect it to the linode?

@Helveticus:

Did you mean that I use MySQL Workbench on my pc and connect it to the linode?
Yes. If your MySQL daemon is not listening for connections on the open internet*, you can use PuTTY to forward a port, creating an ssh tunnel from your local machine to your Linode. Then as described, after connecting you can point MySQL Workbench at 127.0.0.1 and it will be communicating through the tunnel with the MySQL instance on your Linode.

A couple caveats:

1. If you're running MySQL on your Windows client, you likely won't be able to use port 3306 locally. In that case, select another port number as the source port.

2. Don't use "localhost" as the host to connect to in MySQL Workbench. This often will not work as expected. Use the IP address 127.0.0.1. Other applications may have the same problem.

*It really shouldn't be; this is a security hazard. Adjust your bind-address configuration in my.cnf and/or your firewall rules to block outside connections.

@Helveticus:

I know SQL, but I know only how to query the database etc., i.e. select statements and so on. But I don't know how to use it on linode, i.e. how to connect and log in into the database and so on.

ssh to your Linode and use the mysql command:

mysql -u root -p

Type the password, then use it as usual from there.

You can also set it up so you can access it from home, but you're asking for security issues by doing that (unless your home Internet connection has a static IP address).

I'm having a .sql file as a Mysql database backup. Mysql is not yet installed on my linode server.

How can I use this backup?

If MySQL is not installed on your Linode then you can't really use your backup file until you install MySQL on your Linode.

Here's the Linode Library info for MySQL: https://library.linode.com/databases/mysql

I will install MySQL on my linode. ;)

But how can I then use my backup file? This is not written in your link.

You'll need to upload your file to your Linode (after you've edited it since you've already got it on your computer).

Depending on the config of phpmyadin you may be able to import a file as large as 200MB, but don't count on it. You should google how to import your mysql file into mysql from the command line.

http://www.ozerov.de/bigdump/ could help

I found the following tutorial. https://library.linode.com/databases/my … up-options">https://library.linode.com/databases/mysql/backup-options

But I don't know if a backup of an entire DBMS or only of a single database. If I open the .sql backup in a text editor, the first lines are the following:

> – MySQL dump 10.13 Distrib 5.5.29, for debian-linux-gnu (i686)

--

-- Host: localhost Database: xxx

---

-- Server version 5.5.29-0ubuntu0.12.04.2

/*!40101 SET @OLDCHARACTERSETCLIENT=@@CHARACTERSET_CLIENT */;

/*!40101 SET @OLDCHARACTERSETRESULTS=@@CHARACTERSET_RESULTS */;

/*!40101 SET @OLDCOLLATIONCONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLDTIMEZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLDUNIQUECHECKS=@@UNIQUECHECKS, UNIQUECHECKS=0 */;

/*!40014 SET @OLDFOREIGNKEYCHECKS=@@FOREIGNKEYCHECKS, FOREIGNKEY_CHECKS=0 */;

/*!40101 SET @OLDSQLMODE=@@SQLMODE, SQLMODE='NOAUTOVALUEONZERO' */;

/*!40111 SET @OLDSQLNOTES=@@SQLNOTES, SQLNOTES=0 */;

--

-- Table structure for table admin

--

DROP TABLE IF EXISTS admin;

Can somebody say what type of backup it is?

It looks like it's just a dump of database 'xxx'.

@vonskippy:

Phpmyadmin has had a dubious security track record, and personally, I don't see the need for a FisherPrice-esque web interface just to manage your databases.

Either learn MySQL command line (it isn't rocket science), or use a client based GUI like HeidiSQL or SQLyog (both are WAAAAAAY more secure then a web based db interface).

MySQL commands aren't that difficult to learn, and the command line isn't that complex, though a lot of people seem to think it is. I don't have any control panel installed, so I do everything from the command line. I recently watched a video where someone called making a file executable with Terminal "crazy hacker stuff." ಠ_ಠ I prefer to manage databases with a GUI though.

This might still leave vulnerabilities, but you can and should use self signed SSL and .htaccess to restrict PHPmyAdmin to IP addresses of administrators. Is there any way that IP addresses can be faked for this purpose or a way that a hacker could get around this? It helps a lot, but I wouldn't assume it to be totally secure.

I blame a lot of web based stuff for compromising security in the name trying to save time or to make things more convenient. Wordpress allowing admins to edit PHP files from the web is one of those foolish things. I also blame the web FTP managers provided by shared hosting companies for this; they're difficult to navigate to, and they make noobs who see SSH as "crazy hacker stuff" do things like install file upload extensions in their web apps. Learn SCP. Not rocket science.

If you want to use a gui (which isn't a bad thing when it comes to sql). Use MySQL workbench http://www.mysql.com/products/workbench/. It's free, cross-platform, doesn't require anything extra installed on the server and connects via ssh so it's secure.

So it's for your own computer? Doesn't that require making MySQL remotely accessible?

EDIT

I just got it set up without remotely accessible MySQL. I did SSH and then localhost connection.