[mysql] How do I turn off autocommit for a MySQL client?

I have a web app that has been written with the assumption that autocommit is turned on on the database, so I don't want to make any changes there. However all the documentation I can find only seems to talk about using init_connect on the database, i.e. a global setting for all client connections.

Is there a way to set autocommit=0 just when running mysql on a Linux command line (without having to type it in every time)?

For auto commit off then use the below command for sure. Set below in my.cnf file:

    [mysqld]
    autocommit=0

It looks like you can add it to your ~/.my.cnf, but it needs to be added as an argument to the init-command flag in your [client] section, like so:

[client]
init-command='set autocommit=0'

Do you mean the mysql text console? Then:

START TRANSACTION;
  ...
  your queries.
  ...
COMMIT;

Is what I recommend.

However if you want to avoid typing this each time you need to run this sort of query, add the following to the [mysqld] section of your my.cnf file.

init_connect='set autocommit=0'

This would set autocommit to be off for every client though.

Instead of switching autocommit off manually at restore time you can already dump your MySQL data in a way that includes all necessary statements right into your SQL file.

The command line parameter for mysqldump is --no-autocommit. You might also consider to add --opt which sets a combination of other parameters to speed up restore operations.

Here is an example for a complete mysqldump command line as I use it, containing --no-autocommit and --opt:

mysqldump -hlocalhost -uMyUser -p'MyPassword' --no-autocommit --opt --default-character-set=utf8 --quote-names  MyDbName  >  dump.sql

For details of these parameters see the reference of mysqldump

You do this in 3 different ways:

1. Before you do an INSERT, always issue a BEGIN; statement. This will turn off autocommits. You will need to do a COMMIT; once you want your data to be persisted in the database.

2. Use autocommit=0; every time you instantiate a database connection.

3. For a global setting, add a autocommit=0 variable in your my.cnf configuration file in MySQL.

This is useful to check the status of autocommit;

select @@autocommit;