Copy an existing MySQL table to a new table
This is a great set of two commands that allow the creation and population of a new table with the structure and data of an existing table. This provides a quick means of making a point-in-time copy of a table and is a safe, easy way to make a quick copy of a table for testing an application in development on live data without risking a production environment.
To make a copy of the table recipes which is in a different database called production into a new table called recipes_ new in the currently selected database, use these two commands:
CREATE TABLE recipes_ new LIKE production.recipes;
INSERT recipes_ new SELECT * FROM production.recipes;
INSERT recipes_ new SELECT * FROM production.recipes;
The first command creates the new table recipes_new by duplicating the structure of the existing table. The second command copies the data from old to new.
The nomenclature production.recipes is a means of specifying the database and table in the same way that a file can be specified by its directory path. It is optional. If production was left off, MySQL would assume that the recipes table was also in the currently selected database.
Also see ...
Connect to a MySQL server using the mysql command
H3The mysql command provides a text based interface into the MySQL database server. Once connected, SQL commands can be issued to the server to make queries, create or alter databases or tables, or many other operations./H3PTo connect to the MySQL server on the local system, issue the followi
H3The mysql command provides a text based interface into the MySQL database server. Once connected, SQL commands can be issued to the server to make queries, create or alter databases or tables, or many other operations./H3PTo connect to the MySQL server on the local system, issue the followi
Dump Content using mysqldump
H3Dump content of a database in MySQL to a text file. Will prompt for password of account used with ' p'/H3Pmysqldump u [username] p [database_name] /path/to/file.sql br / br /Example: mysqldump u fred p FredsAddresses /tmp/addresses.sql/P
H3Dump content of a database in MySQL to a text file. Will prompt for password of account used with ' p'/H3Pmysqldump u [username] p [database_name] /path/to/file.sql br / br /Example: mysqldump u fred p FredsAddresses /tmp/addresses.sql/P
Change the MySQL root user password
H3Change the root user password for MySQL using mysqladmin/H3PTo change the MySQL root password to PaSsWoRd, use: br / br /div class="code"mysqladmin u root password PaSsWoRdP/P
H3Change the root user password for MySQL using mysqladmin/H3PTo change the MySQL root password to PaSsWoRd, use: br / br /div class="code"mysqladmin u root password PaSsWoRdP/P
MySQL - Daily database dumps, all nicely sorted.
H3A nice shell script that can be run as a cron job to produce nice MySQL dumps for backup. /H3PI run several boxes with MySQL installed. My backup strategy for MySQL includes the following: br / br / Each database needs to be dumped nightly to it's own file. br / I keep all miscel
H3A nice shell script that can be run as a cron job to produce nice MySQL dumps for backup. /H3PI run several boxes with MySQL installed. My backup strategy for MySQL includes the following: br / br / Each database needs to be dumped nightly to it's own file. br / I keep all miscel
Select a MySQL database to use
H3A single instance of MySQL can hold many separate databases. It is important to specify the database to which subsequent commands will be issued./H3PFrom within the mysql command (see a href="mysql_tips271.html" target="_blank" class="postlink" rel="nofollow"Connect to a MySQL server using
H3A single instance of MySQL can hold many separate databases. It is important to specify the database to which subsequent commands will be issued./H3PFrom within the mysql command (see a href="mysql_tips271.html" target="_blank" class="postlink" rel="nofollow"Connect to a MySQL server using