try this.
There are in general three ways to use mysqldump—

in order to dump a set of one or more tables,
 
    shell&gt; mysqldump [options] db_name [tbl_name ...]

a set of one or more complete databases

    shell&gt; mysqldump [options] --databases db_name ...

 or an entire MySQL server—as shown here:

    shell&gt; mysqldump [options] --all-databases

Quoting this link:
[http://steveswanson.wordpress.com/2009/04/21/exporting-and-importing-an-individual-mysql-table/][1]

 - Exporting the Table

To export the table run the following command from the command line:

    mysqldump -p --user=username dbname tableName &gt; tableName.sql

This will export the tableName to the file tableName.sql.

 - Importing the Table

To import the table run the following command from the command line:

    mysql -u username -p -D dbname &lt; tableName.sql

The path to the tableName.sql needs to be prepended with the absolute path to that file. At this point the table will be imported into the DB.

  [1]: http://steveswanson.wordpress.com/2009/04/21/exporting-and-importing-an-individual-mysql-table/

Here I am going to export 3 tables from database named myDB in an sql file named table.sql  

    mysqldump -u root -p myDB table1 table2 table3 &gt; table.sql



In case you encounter an error like this

&gt; mysqldump: 1044 Access denied when using LOCK TABLES

A quick workaround is to pass the `–-single-transaction` option to `mysqldump`.

So your command will be like this.

`mysqldump --single-transaction -u user -p DBNAME &gt; backup.sql`

I would like to see an example of how to call using `bind_result` vs. `get_result` and what would be the purpose of using one over the other.

Also the pro and cons of using each.

What is the limitation of using either and is there a difference.

Example of how to use bind_result vs get_result

How can I prevent a specific plot to be shown in Jupyter notebook? I have several plots in a notebook but I want a subset of them to be saved to a file and not shown on the notebook as this slows considerably.

A minimal working example for a Jupyter notebook is:

    %matplotlib inline 
    from numpy.random import randn
    from matplotlib.pyplot import plot, figure
    a=randn(3)
    b=randn(3)
    for i in range(10):
        fig=figure()
        plot(b)
        fname=&#39;s%03d.png&#39;%i
        fig.savefig(fname)
        if(i%5==0):
            figure()
            plot(a)

As you can see I have two types of plots, a and b. I want a&#39;s to be plotted and shown and I don&#39;t want the b plots to be shown, I just want them them to be saved in a file. Hopefully this will speed things a bit and won&#39;t pollute my notebook with figures I don&#39;t need to see.

Thank you for your time

prevent plot from showing in jupyter notebook

I was using mysqldump to export the database, like this:

    mysqldump -u root -ppassword my_database &gt; c:\temp\my_database.sql

Somehow, it only exports one table. Is there something I&#39;m doing wrong?

mysqldump exports only one table

I was using mysqldump to export the database, like this:
<pre><code class="hljs language-r">mysqldump -u root -ppassword my_database > c:\temp\my_database.sql
</code></pre>
Somehow, it only exports one table. Is there something I'm doing wrong?

I have just switched from a MAMP installation to a native Apache, MySql and PHP installation. I have got everything working, but I have started using my web app in the new environment and suddenly any INSERT commands are resulting in the following error:

&gt; SQLSTATE[HY000]: General error: 1364 Field &#39;display_name&#39; doesn&#39;t have a default value

It seems the I am unable to leave a field blank now where I was able to before. I am using MySql version 5.6.13

Is there a way to change this setting in MySql?

MySql Error: 1364 Field &#39;display_name&#39; doesn&#39;t have default value

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I&#39;m  trying is this

    SELECT trecord FROM `tbl` WHERE (trecord LIKE &#39;ALA[d]%&#39;)

And

    SELECT trecord FROM `tbl` WHERE (trecord LIKE &#39;ALA[0-9]%&#39;)

But both of the queries always return a `null` record 

    trecord
    -------
    null

Where as if I execute the following query

    SELECT trecord FROM `tbl` WHERE (trecord LIKE &#39;ALA%&#39;)

it returns

    trecord
    -------
    ALA0000
    ALA0001
    ALA0002

It means that I have records that starts with ALA and a digit after it,

**EDIT**

I&#39;m doing it using PHP MySQL and innodb engine to be specific.

How to regex in a MySQL query

I tried the following - 

I created a variable at the command prompt as follows - 

    mysql&gt; set @myId = 1;
    Query OK, 0 rows affected (0.00 sec)

Then, to display it, I tried the following without success - 

        mysql&gt; show myId;
        ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
    corresponds to your MySQL server version for the right syntax to use near &#39;myId&#39; at line 1
        mysql&gt; show @myId;
        ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near &#39;@myId&#39; at line 1
        mysql&gt; PRINT @myId;
        ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
    corresponds to your MySQL server version for the right syntax to use near &#39;PRINT @myId&#39; at line 1
        mysql&gt; PRINT myId;
        ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
    corresponds to your MySQL server version for the right syntax to use near &#39;PRINT myId&#39; at line 1

So how can I display the value of `@myId`? 

How to display the value of a variable at the commandline in MySQL?

I am trying to execute the sql query:

    select * from table where column like &#39;%value%&#39;;

But the data is saved as &#39;Value&#39; ( V is capital ).

When I execute this query i don&#39;t get any rows. 
How do i make the call such that, it looks for &#39;value&#39; irrespective of the casing of the characters ?

SQL SELECT LIKE (Insensitive casing)

I have experience with MS SQL server where it is possible and useful to [update statistic][1] and [rebuild indexes][2]. I can&#39;t find such option in MySQL innoDB, is there such option? If not, how MySQL database create an execution plan? Does the MySQL update indexes and statistic with every UPDATE and INSERT?


  [1]: https://msdn.microsoft.com/en-us/library/ms187348.aspx?f=255&amp;MSPPError=-2147217396
  [2]: https://msdn.microsoft.com/en-us/library/ms189858.aspx

How can I rebuild indexes and update stats in MySQL innoDB?

I&#39;m trying to export my database, using MySQL Workbench 6.0 on Windows, to send to my db instance in Amazon RDS, but i&#39;m getting this error:

    Operation failed with exitcode 7
    11:34:40 Dumping clubbin (taxicompanies)
    Running: &quot;C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe&quot;
    -defaults-extra- file=&quot;c:\users\selene\appdata\local\temp\tmp6o0hno.cnf&quot;
    --max_allowed_packet=1G --delayed-   insert=FALSE --host=localhost --user=root
    --port=3306 --default-character-set=utf8 &quot;clubbin&quot;   &quot;taxicompanies&quot;
    mysqldump: [ERROR] unknown variable &#39;delayed-insert=FALSE&#39;

Does anybody have any idea that can help me?

Export Data from mysql Workbench 6.0

I am having problems with getting a `crontab` to work. I want to automate a MySQL database backup.

The setup:

 - Debian GNU/Linux 7.3 (wheezy) 
 - MySQL Server version: 5.5.33-0+wheezy1(Debian) 
 - directories user, backup and backup2 have 755 permission
 - The user names for MySQL db and Debian account are the same

From the shell this command works

	mysqldump -u user -p[user_password] [database_name] | gzip &gt; dumpfilename.sql.gz


When I place this in a crontab using crontab -e 

	* * /usr/bin/mysqldump -u user -pupasswd mydatabase | gzip&gt; /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz &gt;/dev/null 2&gt;&amp;1

A file is created every minute in /home/user/backup directory, but has 0 bytes.
 
However when I redirect this output to a second directory, backup2, I note that the proper mysqldumpfile duly compressed is created in it. I am unable to figure what is the mistake that I am making that results in a 0 byte file in the first directory and the expected output in the second directory.

	* * /usr/bin/mysqldump -u user -pupasswd my-database | gzip&gt; /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz &gt;/home/user/backup2/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz 2&gt;&amp;1

I would greatly appreciate an explanation.

Thanks



mysqldump &amp; gzip commands to properly create a compressed file of a MySQL database using crontab

I have an existing MySQL instance (test), containing 2 databases and a few users each having different access privileges to each database.

I now need to duplicate one of the databases (into production) **and the users** associated with it.

Duplicating the database was [easy][1]:

Export:

    mysqldump --no-data --tables -u root -p secondb &gt;&gt; secondb_schema.sql

Import:

    mysql -u root -p -h localhost secondb &lt; secondb_schema.sql


I didn&#39;t find, however, a straightforward way to export and import **users**, from the **command line** (either inside or outside mysql).

How do I export and import a [user][2], from the command line?

---------

**Update**: So far, I have found manual (and thus error prone) steps for accomplishing this:

    -- lists all users
    select user,host from mysql.user;

Then find its grants:

    -- find privilege granted to a particular user
    show grants for &#39;root&#39;@&#39;localhost&#39;; 


Then **manually** create user with the grants listed in the result of the &#39;show grants&#39; command above. 

I prefer a safer, more automated way. Is there one?

  [1]: https://stackoverflow.com/a/21091197/1864054
  [2]: https://stackoverflow.com/a/1135251/1864054




Content Type	Original Author	Original Content on Stackoverflow
Question	danieln	View Question on Stackoverflow
Solution 1 - Mysql	developerCK	View Answer on Stackoverflow
Solution 2 - Mysql	Piero Alberto	View Answer on Stackoverflow
Solution 3 - Mysql	Nids Barthwal	View Answer on Stackoverflow
Solution 4 - Mysql	Rich	View Answer on Stackoverflow

mysqldump exports only one table

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

prevent plot from showing in jupyter notebook

Example of how to use bind_result vs get_result

Attributions