How add 1billion rows – Mysql

in MySQL 5.7 this would work

delimiter //

CREATE PROCEDURE populate (IN num INT)
       BEGIN
          WHILE num > 0 DO
            START TRANSACTION;
            INSERT INTO users (username, email, validts, confirmed)
            SELECT 
                CONCAT('user', cast(num as CHAR)),
                CONCAT('user', cast(num as CHAR), '@example.com'),
                FROM_UNIXTIME(UNIX_TIMESTAMP() + 2592000),
                0;
            COMMIT;
            SET num = num - 1;
          END WHILE;
       END//

delimiter ;

call populate(1000000000);

but this will take very long time. you may try to move START TRANSACTION before WHILE loop, so MySQL will do one big transaction instead of 1 billion of small ones. That will speed it up a little bit, but in case of failure or Ctrl-C it will rollback all the inserted lines. Also disabling indexes on the table and disabling binlog would help a little bit. Beware, disabling binlog will break replication, if it takes place.
In more recent version you might be able to achieve this with recursive CTE. You generate CTE recursively until reaching 1 billion, and then insert into table from it. I think this should be faster.