mysql: Dumping single database table and restoring

Sometimes you need to dump a single table from a mysql table and restore it somewhere else. Here's what you need:

 

1. Dump the table (compress it so it will take shorter to transfer over the network)

 

mysqldump -h <HOST> -u <USERNAME> -p <DATABASE> <TABLE> | gzip > <DUMP_FILENAME>.gz

 

HOST is the hostname or IP address of the mysql server

USERNAME is a user with access to the database and table

DATABASE and TABLE are self explanatory

DUMP_FILE is the name you want for the file containing the compressed dump

 

2. Transfer the file where you need it to be, with "cp" or "scp"

 

 

cp <DUMP_FILENAME>.hz  folder/new_name

scp <DUMP_FILENAME>.hz  user@machine:folder

 

 

Raspberry Pi: installing a LAMP server

Let's put the Raspberry Pi to good use:

 

1. Install Mysql

sudo pacman -S mysql

2. Start your server

# systemctl start mysqld

3. Secure your mysql installation

# /usr/bin/mysql_secure_installation

——————-

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 … Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 … Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 … Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 – Dropping test database…
 … Success!
 – Removing privileges on test database…
 … Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 … Success!

Cleaning up…

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

———————-

 

# nano /etc/mysql/my.cnf

 

[mysqld]

bind-address=127.0.0.1

 

 

Restart mysql

systemctl restart mysqld

 

 

================

Install PHP

 

pacman -S php php-apache libmcrypt libtool

 

nano /etc/php/php.ini

 

 

 

======================

 

nano /etc/locale.gen

enable:

en_US.UTF-8 UTF-8
en_US ISO-8859-1

 

save and generate your locales

# locale-gen

 

 

Install and Configure PostgreSQL

pacman -S postgresql

 

# mkdir -p /var/lib/postgres/data

# chown httpd:httpd -Rv /var/lib/postgres/

 

Configure Postgresql data dir

# su – postgres -c "initdb –locale en_US.UTF-8 -D '/var/lib/postgres/data'"

———————–

The files belonging to this database system will be owned by user "httpd".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/postgres/data … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 24MB
creating configuration files … ok
creating template1 database in /var/lib/postgres/data/base/1 … ok
initializing pg_authid … ok
initializing dependencies … ok
creating system views … ok
loading system objects' descriptions … ok
creating collations … ok
creating conversions … ok
creating dictionaries … ok
setting privileges on built-in objects … ok
creating information schema … ok
loading PL/pgSQL server-side language … ok
vacuuming database template1 … ok
copying template1 to template0 … ok
copying template1 to postgres … ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
–auth-local and –auth-host, the next time you run initdb.

Success. You can now start the database server using:

    postgres -D /var/lib/postgres/data
or
    pg_ctl -D /var/lib/postgres/data -l logfile start
 

——————————————

 

Enable the service

# systemctl enable postgresql

Start if you want to use it before rebooting

# systemctl start postgresql

 

Setting up a Debian box for development: making a LAMP: mysql

Let's make a lamp

 

First M for Mysql:

# apt-get install mysql-server

The script will ask you to define the password for the root mysql user:

Secure your mysql installation:

# mysql_secure_installation

 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 … skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 … Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 … Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 – Dropping test database…
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 … Failed!  Not critical, keep moving…
 – Removing privileges on test database…
 … Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 … Success!

Cleaning up…

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!
 

 

MySQL: Recover root password

 

 

 

 

# mysql -u root
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
 
# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

 

 

1 Stop MySQL server:

# service mysql start

 

2. Add –skip-grant-tables to your mysql start script

# nano /etc/rc3.d/S20mysql

 

——————-

 

case "${1:-''}" in
  'start')
        sanity_checks;
        # Start daemon
        log_daemon_msg "Starting MySQL database server" "mysqld"
        if mysqld_status check_alive nowarn; then
           log_progress_msg "already running"
           log_end_msg 0
        else
            # Could be removed during boot
            test -e /var/run/mysqld || install -m 755 -o mysql -g root -d /var/run/mysqld
 
            # Start MySQL!
            /usr/bin/mysqld_safe –skip-grant-tables > /dev/null 2>&1 &
 
            # 6s was reported in #352070 to be too few when using ndbcluster
            for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
———————
 

 

3. Start your server again

 

# service -mysql start

 

4. Log in using mysql root user without entering a password:

# mysql -u root mysql

 

And let's do the actual reset:

 

——-

 

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.49-3 (Debian)
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> update user set password=Password('1234') where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> exit
Bye
 
——————
 
5. Stop the server one more time
 
# service mysql stop
 
6. Remove –skip-grant-table from your mysql startup command
 
# nano /etc/rc3.d/S20mysql
 
 
7. Start your mysqlserver
 
# service mysql start
 
 
8. Enjoy your server:
 
 
———————-
root@debian:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.49-3 (Debian)
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>
————————-
 

 

My current methods while using Kohana 3.0.8 – part 1

I’ve finally decided to get started on documenting how I’ve been using this wonderful framework.

So if you’re patient enough to deal with my crazy schedule (meaning this series may take a while to be finish), fasten your seatbelt:

First of all:

  • Know your environment (your public html folder, a private [but still accessible] folder; permissions etc);
  • Know the difference between server side and client side programming;
  • Download Kohana (http://kohanaframework.org/) 🙂
  • Have your favorite source code editor and ftp client close to you

For the sake of whoever wants to get started with Kohana, I’ll go over the basics (not programming) and I’ll also cover everything that got me breaking my head, so I have a copy outside my brain.

[To-Do: find short video of Sean Connery’s character explaining to Harrison Ford’s character why he wrote everything down on a book]

(gs)MT- REQUEST ID #680057 / STATUS: Pending Resolution

James S.

Thank you for the phone call. Here is a brief summary of our conversation:

You called because you were unable to access phpMyAdmin and phpPgAdmin from your AccountCenter and could not connect to the databases through a SSH client.

While looking into this issue I found that the migration from the MySQL Grid-Container back into the MySQL SmartPool failed due to a failed SSH connection during the process.  I was able to contact a systems administrator who started the migration process again, as we spoke we were able to verify that the migration had been completed and we had access to both phpMyAdmin and phpPgAdmin.

You mentioned that you thought this script should be updated so that if it fails such as it did earlier today it would automatically be run again.  A note of this has been made and sent to the (mt) Media Temple requests department for further review.

During the course of this call we also reviewed some issues with support requests regarding Account: 144100.  At this time I transferred you to speak with my lead technician to get more information regarding the status of the pg_dump error.

If you have any further questions regarding your (mt) Media Temple services, please feel free to contact us at any time.

Best Regards,

James Starbuck
Customer Support
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007
Check out our New User Forums!
http://kb.mediatemple.net/questions/824/

@
2009.10.23 05:54 PM
I’m waiting on this one

(gs)MT- REQUEST ID #657010 / STATUS: Resolved

Robert V.

Thank you for the phone call. Here is a brief summary of our conversation:

Due to the repeated inconvenience of your PostgreSQL database running out of memory intermittently since you started this account, I am reassigning this Support Request to our Billing Department to add a database container to your account.  The first month will be free.  If you decide that you do not want to keep the container you will need to close the container before the month is over.  If you do not close the container before the month is over your account will be assessed a fee for the container’s usage.

Thank you for using (mt) Media Temple!  Feel free to let us know if there is anything else we can help you with.

Best Regards,

Robert V.
Customer Support
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007

NEW! User Forums: http://kb.mediatemple.net/questions/824
Knowledge base: http://kb.mediatemple.net/

@
2009.09.17 01:40 PM
Shawnte A.

We have added a MySQL Container Lite to your account.  The billing cycle for this service is 10/25/09 at which time you will be charged $20.00.  If you have any additional questions or concerns, please feel free to contact us.

Best Regards,

Shawnte` A.
Billing Administrator
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007

@
2009.09.17 03:38 PM
James S.

Thank you for the phone call. Here is a brief summary of our conversation:

You called because your databases were moved into a MySQL GridContainer,and since this has occurred your sites running in a PostgreSQL database are unable to resolve to the database content.  An error occurred when the databases were moved to the container which caused the tables in your PostgreSQL databases not to be added to the databases when they were created.

In order to have this issue resolved I have reassigned this support request to a higher level employee to resolve.  Please anticipate a delay while this support request is reassigned and resolved.

If you have any further questions regarding your (mt) Media Temple services, please feel free to contact us at any time.

Best Regards,

James Starbuck
Customer Support
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007
Check out our New User Forums!
http://kb.mediatemple.net/questions/824/

@
2009.09.17 05:32 PM
Maurício

In case I choose not to keep the Container, will my databases be moved automatically to the regular pool?

@
2009.09.17 05:33 PM
Michael H.

You were having a problem with your databases not being populated with tables after the GridContainer move. Our admins were able to recover your data and I restored the tables for you. We don’t currently have an answer for the original issue, but our engineers are working on it. You should receive an email once they have it resolved.

If you have any further questions regarding your (mt) Media Temple services, please feel free to contact us at any time.

Best Regards,

Mike H.
Customer Support
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007

NEW! User Forums: http://kb.mediatemple.net/questions/824/

@
2009.09.17 08:51 PM
Mike M.

Please refer to your Grid-Service "aliofthevalley.com", Support Request # 657007, for the response to this Support Request. If you have any further questions regarding your (mt) Media Temple services, please feel free to contact us at any time.

Best Regards,

Mike M
Customer Support
Check out the New User Forums: http://kb.mediatemple.net/questions/824/
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007

@
2009.09.17 11:17 PM
Travis O.

Thank you for the phone call. Here is a brief summary of our conversation:

You called because you were unhappy about the way your PostgreSQL databases were bursted and you lost the complete functionality of your databases.

We have determined that your request requires the assistance of a higher level staff member or system administrator. Please anticipate a short delay while we re-assign your support request to a staff member who is better equipped to service your needs.

Due to the nature of this support request additional time may be needed to provide you with a response. Thank you.

Best Regards,

Travis Oberlander
Customer Support
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007

@
2009.09.24 10:54 AM
Nathaniel M.

Thank you for the phone call. You called in because you wanted to speak with a supervisor about an issue related to your pg databases. I asked if you could submit a support request with the exact problem so I could escalate it if necessary and you asked to speak to a supervisor. I then transferred you to TJ.

Best Regards,

Nat M
Customer Support
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007
User Forums: http://kb.mediatemple.net/questions/824/

@
2009.09.24 04:49 PM
Maurício

I understand I received a month credit for a Grid Container because of the allocate memory problems (which happen when trying to connect to the database server; and not white running any query either complex or simple)

I’d like to know if I could get any compensation on the fact that my databases were not moved correctly to the Grid Container.

@
2009.09.24 05:14 PM
Daniel C.

Thank you for the phone call. Here is a brief summary of our conversation:

We spoke today regarding issues connecting to your database externally. I was able to duplicate this issue and explained that an administrator will take a look into this shortly.

If you have any further questions regarding your (mt) Media Temple services, please feel free to contact us at any time.

Best Regards,

Daniel C.
Customer Support
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007

NEW! User Forums: http://forums.mediatemple.net

@
2009.09.24 06:23 PM
Daniel C.

Please use support request #661555 for further information on the external connection issue.

Best Regards,

Daniel C.
Customer Support
(mt) Media Temple
<v> 877-578-4000
<f> 310-564-2007

NEW! User Forums: http://forums.mediatemple.net

@
2009.09.24 08:10 PM
  • IGlr