mysqlpump — A Database Backup Program

2 comments
The MySQL 5.7 Release Notes  for version 5.7.8 are out. Besides the new JSON data type, there is also a new tool, called mysqlpump, which offers the following features:

  • Parallel processing of databases, and of objects within databases, to speed up the dump process
  • Better control over which databases and database objects (tables, views, stored programs, user accounts) to dump
  • Dumping of user accounts as account-management statements (CREATE USER, GRANT) rather than as inserts into the mysql system database
  • Capability of creating compressed output
  • Progress indicator
  • For dump file reloading, faster secondary index creation for InnoDB tables by adding indexes after rows are inserted
This is great stuff, however it's still a release candidate so let me point out a caution warning left by Morgan Tocker on his blog:
... mysqlpump is not currently consistent. That is to say that currently each of the dump threads lack a synchronization point before they start backing up the data. This makes it currently unsafe as a general purpose backup replacement.
Happy testing!

2 comments :

Post a Comment

Creating user accounts on a secured MySQL server

1 comment
After installing a MySQL database server and securing that installation with the mysql_secure_installation tool, you are locked out from remote access to perform any operation on the server. Since we all like the 'R' in RDBMS to stand for remote as well as relational, let's see how we can configure user credentials to provide remote access to the database server but still keep those credentials from providing local access.
Suppose you have a key user named James Bond to whom you want to give remote administration on the MySQL server. However, you don't want him to logon locally on the machine to perform any operations. This way you achieve three levels of security:
  • The credential only allows remote access;
  • You control from which remote computers the user can connect, by configuring rules on a network or server firewall;
  • IF it's a shared server, console access on the machine doesn't give access to the database engine.
First, let's open a terminal session on the server and a login with the mysql command line tool, using our root account:
shell> mysql -u root -p
Now, for the sake of this example, let's create a user account for James Bond and give him full privileges:
mysql> CREATE USER 'jamesbond'@'%' IDENTIFIED BY 'double0seven';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'jamesbond'@'%' WITH GRANT OPTION;
Having set this up, if James Bond tries to login from any remote computer he can because we used the wildcard '%' to specify the user's machine. But he can also login from the local server. To prevent this from happening, we create a second account with the same username and password, but do not grant any permissions:
mysql> CREATE USER 'jamesbond'@'localhost' IDENTIFIED BY 'double0seven';
If the user then tries to login again from the local machine, he can. But isn't able to perform any operations or queries.

1 comment :

Post a Comment